I got ” ORA-04052: error occurred when looking up remote object” error in Oracle database.
ORA-04052: error occurred when looking up remote object
Details of error are as follows.
ORA-04052 error occurred when looking up remote object Cause: An error has occurred when trying to look up a remote object. Action: Fix the error. Make sure the remote database system has run KGLR.SQL to create necessary views used for querying/looking up objects stored in the database.
ERROR at line 1: ORA-04052: error occurred when looking up remote object APPS.EDW_HR_ASCH_ASG_CHNG_LCV@APPS_TO_APPS.AAAA.COM ORA-00604: error occurred at recursive SQL level 1 ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
error occurred when looking up remote object
This ORA-04052 errors are related with the error has occurred when trying to look up a remote object.
This is due to invalid database link APPS_TO_APPS.AAAA.COM.
To solve this error, Recreate the database link using the following:
1. Drop database link by issuing the following command:
drop database link <link_name>;
2. Recreate the database link by executing the following command:
create database link <link_name> connect to <apps_schema normally apps> identified by <apps_schema normally apps> using '<db_name>';
3. Recompile EDW_HR_ASG_CHNG_M_C by issuing the following command:
alter package EDW_HR_ASG_CHNG_M_C compile body;
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )