Skip to Main Content

Developer Community

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

transferring records using exchange partition between two different database

I have one table T1 in db1 which is list partitioned table. And T2 in db2 which is non partitioned table.dblink is created at db1 and i am able to execute below mentioned query
select * from t2@dblink;

Alter table t1 exchange partition part_name
WITH TABLE T2@dblink
WITHOUT VALIDATION;
This is giving an error that exchange partition cannot be done over dblink.
I have googled and found the solution as

exec dbms_utility.exec_ddl_statement@db_link('your statment');

but i am not getting how to fit this query for my scenario.

begin
dbms_utility.exec_ddl_statement@dblink('alter table T1 exchange partition part_name
with table T2@DBLINK
WITHOUT VALIDATION ');

end ;

and getting this error

Error report -
ORA-02021: DDL operations are not allowed on a remote database
ORA-06512: at "SYS.DBMS_UTILITY", line 593
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "SYS.DBMS_UTILITY", line 586
ORA-06512: at line 2
02021. 00000 - "DDL operations are not allowed on a remote database"
*Cause: An attempt was made to use a DDL operation on a remote database.
For example, "CREATE TABLE tablename@remotedbname ...".
*Action: To alter the remote database structure, you must connect to the
remote database with the appropriate privileges.

Comments
Post Details
Added on Mar 14 2024
0 comments
92 views