ORA-14100: partition extended table name cannot refer to a remote object

I got ” ORA-14100: partition extended table name cannot refer to a remote object ” error in the Oracle database when Select Partition of table using DBLink (  database link ) in Oracle.

ORA-14100: partition extended table name cannot refer to a remote object

Details of error are as follows.

SQL> insert into MSDBA.TEST_TABLE select /*+ parallel 64 */ * from MSDBA.TEST_TABLE@READ_LINK PARTITION(P_20211225);
insert into MSDBA.TEST_TABLE select /*+ parallel 64 */ * from MSDBA.TEST_TABLE@READ_LINK PARTITION(P_20211225)
*
ERROR at line 1:
ORA-14100: partition extended table name cannot refer to a remote object


SQL>

This ORA-14100 error is related with the Database link usage with Partititioned table. Select Partition via dblink is not supported in Oracle.

This is a restriction with the usage of partition-extended and sub partition-extended table over the database link (dblink).

Partition clause is not supported over the dblink.

As a solution perform the following :

Create the following view on Source database (  remote side ).

SQL> create view MSDBA.TEST_VIEW as select /*+ parallel 64 */ * from MSDBA.TEST_TABLE PARTITION(P_20211225);

View created.

SQL>

Then Use this VIEW instead of partition of table as follows.

SQL> insert into MSDBA.TEST_TABLE select /*+ parallel 64 */ * from MSDBA.TEST_VIEW@READ_LINK;
commit;

Bug:9093962  PARTITION CLAUSE IGNORE WHEN THERE IS A SYNONYM TO A REMOTE TABLE Fixed In Ver: 11.1.0.7

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )