I am running an impdp job that I have extracted the DDL from to determine why the job hangs. It consistently stops on the first object created using a dblink. I am running the impdp job as system as recommended by Oracle. The job must be run on two separate, different instances on the same database using a unique dump file for each instance. The impdp job only imports one schema from the dump file, the same schema name, in both instances. The two impdp jobs are interdependent on dblinks. At the point where the job is indefinitely waiting on either impdp instance job, it is unable to retrieve data from the specified dblink, as confirmed per DDL extract run on SQL Developer. The links have been sucessfully created in each database instance and
SELECT * FROM DUAL@DB_LINK returns a value but
SELECT * FROM 'view_name'@DB_LINK returns 'ORA-00942: table or view does not exist', although I have confirmed the view exists in the remote schema. I have researched creating and fetching data via dblinks and understand the dblink querying user must be granted access to the remote object(s). In this case the user is the same with identical passwords on both database instances. How can I grant permission on all schema objects to the user of same name in separate instance so that the DDL can complete?
UPDATE: I have granted access to all views in instance1 to the connect as user for the dblink utilized by the user instance2 and vice versa. I still cannot select views from either instance using the dblinks from respective remote users.This is the script:
BEGIN FOR objects IN ( SELECT 'GRANT ALL ON "'||owner||'"."'||object_name||'" TO <DB_LINK_NAME>' grantSQL FROM all_objects WHERE owner = 'OWNER_NAME' AND object_type = 'VIEW' AND object_type IN ( select view_name from sys.dba_views where owner = 'OWNER_NAME' ) ORDER BY object_type, object_name ) LOOP BEGIN EXECUTE IMMEDIATE objects.grantSQL; EXCEPTION WHEN OTHERS THEN --Ignore ORA-04063: view "X.Y" has errors. --(You could potentially workaround this by creating an empty view, -- granting access to it, and then recreat the original view.) IF SQLCODE IN (-4063) THEN NULL; --Raise exception along with the statement that failed. ELSE raise_application_error(-20000, 'Problem with this statement: ' || objects.grantSQL || CHR(10) || SQLERRM); END IF; END; END LOOP; END; /