DDL Execution in Oracle Sharded Database
The Shard catalog database has copies of all the objects present in sharded database. If catalog validate the DDL then it applied on all shards(database).
If new shard is added then from catalog database it will propagate the ddls to all the shards (databases).
Two ways you can issue DDLs in an SDB:
1. GDSCTL sql command.
When you execute a command , all shards are updated and return status of execution. A master copy is present in shard catalog.
GDSCTL> sql "create tablespace set tbsset"
2. Connect to the shard catalog database using SQL*Plus using the GDS$CATALOG.sdbname service
When you execute the command, local shards response on execution and all other shards are working inbackgroud.
SQL> create tablespace set tbsset;
For SQL PLUS two type of object is created. SDB object and local object. Local objects are the object that exist in Shard catalog and used for administrative purpose.
Note: type of object (SDB or local) that is created in a SQL*Plus session depends on whether the SHARD DDL mode is enabled in the session. Mode is enabled default if connect with catalog database of SDB user.
--For Disable create local object
alter session disable shard ddl;
--For enable create shared objects
alter session enable shard ddl;
Note: If you direct connected with shared user or catalog, you donot need to enable shared it automatic enabled.
Check Status of DDL Commands:
SHOW DDL and CONFIG SHARD is used from GDSCTL. It will tell you failed shards on which command is failed.
GDSCTL> show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****
Note: IF DDL failed on one shard all further DDL are blocked until failure is resolved (GDSCTL recover shard)
Example For checking the DDL command executed on all shreds:
-- Enable the Shard DDL Session
SQL> alter session enable shard ddl;
-- Fire the Command for creating a User
SQL> CREAT USER example_user IDENTRIFIED BY password1;
CREATE USER example_user IDENTRIFIED BY password1
*
ERROR at line 1:
ORA-00922: missing or invalid option
-- Check the Command show in List.
GDSCTL> show ddl
id DDL Text Failed shards
-- -------- -------------
Note: it show command is not listed means not executed in any Shard(database)
--Fire the create user command again
SQL> CREATE USER example_user IDENTIFIED BY password1;
User created.
-- Show the command is executed successfully
GDSCTL>show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****
