How to Run DDL Command on Remote Database With Database Link

The DDL command cannot be executed directly with the database link on the remote database. If it is run, it will cause the error as follows.

ORA-02021: DDL operations are not allowed on a remote database.

The dbms_utility.exec_ddl_statement procedure must be used for this. With this procedure, any DDL command can be executed in the remote database.

In this way, all operations can be done from a single point.

Example:

SQL> exec dbms_utility.exec_ddl_statement@DB_LINK('TRUNCATE TABLE USER.TABLE_NAME');

DB_LINK specifies the database link. After that, the command is executed in parenthesis and single quotes.

Create a table as follows.

SQL> exec dbms_utility.exec_ddl_statement@DB_LINK('create table emp (id number)');

In this way, it is possible to run DDL commands through the database link in the remote database.

Please ensure that you have the necessary privileges, appropriate database link setup, and proper authorization/grants to execute DDL commands on the remote database.

Caution when performing DDL operations, as they can have significant impacts on the database structure and integrity. Always verify and test your commands thoroughly before executing them.