We have the procedures refresh_materialized_views & submit_refresh_job under user1.xxsl package. When we execute the refresh_materialized_views procedure, the refresh finishes with no errors. But when we execute the submit_refresh_job procedure, which submits the refresh_materialized_views procedure as a dbms_job, we get the error.
XXSL.refresh_materialized_views: -12008-ORA-12008: error in materialized view refresh path
ORA-04052: error occurred when looking up remote object USER1.OBJECT_NAME@DB_LINK
ORA-00604: error occurred at recursive SQL level 3
ORA-12154: TNS:could not resolve the connect identifier specified
Both databases A & B reside on the same server. I looked at the network configuration files and the public DB link from A is testing fine. Not sure what is causing the above TNS error..Any help will be appreciated. The database version is A is 11.2.0.3 and for B is 11.2.0.2 and the OS is HP-UX.