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.