PL/SQL Procedure Execution in a Sharded Database
In the same way that DDL statements can be run on all shards in a sharded database configuration, so too can certain Oracle-provided PL/SQL procedures.
These specific procedure calls behave as if they were sharded DDL statements, in that they are propagated to all shards, tracked by the catalog, and run whenever a new shard is added to a configuration.
All of the following procedures can act as if they were a sharded DDL statement.
- Any procedure in the
DBMS_FGA
package - Any procedure in the
DBMS_RLS
package - The following procedures from the
DBMS_STATS
package:GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DATABASE_STATS
GATHER_SYSTEM_STATS
- The following procedures from the
DBMS_GOLDENGATE_ADM
package:ADD_AUTO_CDR
ADD_AUTO_CDR_COLUMN_GROUP
ADD_AUTO_CDR_DELTA_RES
ALTER_AUTO_CDR
ALTER_AUTO_CDR_COLUMN_GROUP
PURGE_TOMBSTONES
REMOVE_AUTO_CDR
REMOVE_AUTO_CDR_COLUMN_GROUP
REMOVE_AUTO_CDR_DELTA_RES
Note:
Oracle GoldenGate replication support for Oracle Sharding High Availability is deprecated in Oracle Database 21c.
To run one of the procedures in the same way as sharded DDL statements, do the following steps.
-
Connect to the shard catalog database using SQL*Plus as a database user with the
gsm_pooladmin_role
. -
Enable sharding DDL using
ALTER SESSION ENABLE SHARD DDL
. -
Run the target procedure using a sharding-specific PL/SQL procedure named
SYS.EXEC_SHARD_PLSQL
.This procedure takes a single CLOB argument, which is a character string specifying a fully qualified procedure name and its arguments. Note that running the target procedure without using
EXEC_SHARD_PLSQL
causes the procedure to only be run on the shard catalog, and it is not propagated to all of the shards. Running the procedure without specifying the fully qualified name (for example,SYS.DBMS_RLS.ADD_POLICY
) will result in an error.
For example, to run DBMS_RLS.ADD_POLICY
on all shards, do the
following from SQL*Plus after enabling SHARD DLL
.
exec sys.exec_shard_plsql('sys.dbms_rls.add_policy(object_schema =>
''testuser1'',
object_name => ''DEPARTMENTS'',
policy_name => ''dept_vpd_pol'',
function_schema => ''testuser1'',
policy_function => ''authorized_emps'',
statement_types => ''INSERT, UPDATE, DELETE, SELECT, INDEX'',
update_check => TRUE)'
) ;
Take careful note of the need for double single-quotes inside the target procedure
call specification, because the call specification itself is a string
parameter to EXEC_SHARD_PLSQL
.
If the target procedure executes correctly on the shard catalog database, it is queued for processing on all of the currently deployed shards. Any error in the target procedure execution on the shard catalog is returned to the SQL*Plus session. Errors during execution on the shards can be tracked in the same way they are for DDLs.
Parent topic: Sharded Database Schema Design