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 [email protected]_LINK PARTITION(P_20211225);
insert into MSDBA.TEST_TABLE select /*+ parallel 64 */ * from [email protected]_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 :

1. Do not use partition clause when involving database link
— OR —
2. Create a view at the remote site ( Source database ) that uses the extended table name syntax and then refer to the remote view as follows.

 

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 )

 10,047 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published.