Same user cannot ‘see’ view on remote oracle database athough dblink is valid

dblinkimpdporacle-11g-r2

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;
/

Best Answer

Do not quote schema or object names in your SQL. If forces case-sensitivity in the names where none exists by default in Oracle. i.e. TABLE_NAME is the same as table_name, but "TABLE_NAME" is not the same as table_name. Even if you were going to quote the object names to enforce case-sensitivity, you should use double-quotes and not single quotes.

Also, be careful what you're granting: "GRANT ALL" is not appropriate if all the user needs to do is select data.

     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

produces a SQL statement like this:

GRANT ALL ON 'owner'.'object_name' TO DB_LINK_USER_NAME

When what you most likely want is this:

GRANT SELECT ON owner.object_name TO DB_LINK_USER_NAME

Your query also seems a little convoluted. DBA_VIEWS gives you everything you need, so why join with ALL_OBJECTS which may not contain the matching view names? The order of the grants are made is also not important, so why include an ORDER BY clause that will force extra I/O to sort the results in the TEMP tablespace?

Try this:

     SELECT 'GRANT SELECT ON "||owner||"."||object_name||" TO <DB_LINK_USER_NAME>' grantSQL
       FROM dba_views
      WHERE owner = 'OWNER_NAME'
Related Question