ORA-04052: error occurred when looking up remote object

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 )