When making changes to duplicated tables or sharded tables in an Oracle Globally Distributed Database, these changes should be done from the shard catalog database.

Before running any DDL operations on a distributed database, enable sharded DDL with

ALTER SESSION ENABLE SHARD DDL; 

This statement ensures that the DDL changes will be propagated to each shard in the distributed database.

The DDL changes that are propagated are commands that are defined as “schema related,” which include operations such as ALTER TABLE.  There are other operations that are propagated to each shard, such as the CREATE, ALTER, DROP user commands for simplified user management, and TABLESPACE operations to simplify the creation of tablespaces on multiple shards.

GRANT and REVOKE operations can be done from the shard catalog and are propagated to each shard, providing you have enabled shard DDL for the session. If more granular control is needed you can issue the command directly on each shard.

Operations such as DBMS package calls or similar operations are not propagated. For example, operations gathering statistics on the shard catalog are not propagated to each shard.

If you perform an operation that requires a lock on a table, such as adding a not null column, it is important to remember that each shard needs to obtain the lock on the table in order to perform the DDL operation. Oracle’s best practices for applying DDL in a single instance apply to sharded environments.

Multi-shard queries, which are processed on the shard catalog, issue remote queries across database connections on each shard. In this case it is important to ensure that the user has the appropriate privileges on each of the shards, whether or not the query will return data from that shard.