2 Replies Latest reply: Nov 21, 2012 7:57 AM by AlbertoFaenza RSS

    Db link from 11.2.0.2 to 11.2.0.3 version DBs not working properly

    Omega3
      Remote Schema in 11.2.0.3 DB


      In 11.2.0.2 DB , I created a DB link pointing to a schema in an 11.2.0.3 DB
      -- Used the new 11.2 syntax (similar to easy connect )
      
      SQL > CREATE DATABASE LINK dropLater CONNECT TO SCOTT IDENTIFIED BY SCOTT_123 USING 'blpclst-scan.ap.haster.com:3587/SNPG_AP.ap.haster.com';
      
      Database link created.
      
      SQL > select count(*) from ind_names@dropLater;
      select count(*) from ind_names@dropLater
                           *
      ERROR at line 1:
      ORA-00942: table or view does not exist
      ORA-02063: preceding line from DROPLATER
      
      
      
      -- But the Sysdate from dual test is working fine
      
      SQL > select sysdate from dual@DROPLATER;
      
      SYSDATE
      --------------------
      21 Nov 2012 13:24:29
      IND_NAMES Table does exist in the remote schema SCOTT. Anyone faced a similair issue with DB Links?

      I think this issue is due to version difference ( 11.2.0.2 to 11.2.0.3 )
        • 1. Re: Db link from 11.2.0.2 to 11.2.0.3 version DBs not working properly
          EdStevens
          Omega3 wrote:
          Remote Schema in 11.2.0.3 DB


          In 11.2.0.2 DB , I created a DB link pointing to a schema in an 11.2.0.3 DB
          -- Used the new 11.2 syntax (similar to easy connect )
          
          SQL > CREATE DATABASE LINK dropLater CONNECT TO SCOTT IDENTIFIED BY SCOTT_123 USING 'blpclst-scan.ap.haster.com:3587/SNPG_AP.ap.haster.com';
          
          Database link created.
          
          SQL > select count(*) from ind_names@dropLater;
          select count(*) from ind_names@dropLater
          *
          ERROR at line 1:
          ORA-00942: table or view does not exist
          ORA-02063: preceding line from DROPLATER
          
          
          
          -- But the Sysdate from dual test is working fine
          
          SQL > select sysdate from dual@DROPLATER;
          
          SYSDATE
          --------------------
          21 Nov 2012 13:24:29
          IND_NAMES Table does exist in the remote schema SCOTT. Anyone faced a similair issue with DB Links?

          I think this issue is due to version difference ( 11.2.0.2 to 11.2.0.3 )
          No, it has nothing to do with the version and it has nothing to do with the database link. You said it yourself -- "ND_NAMES Table does exist in the remote schema SCOTT"

          So, you are connecting to the remote database as SCOTT, who, by your own admission, does NOT own a table named ND_NAMES. And when you try to query it you get "table or view does not exist"

          What do you expect?
          • 2. Re: Db link from 11.2.0.2 to 11.2.0.3 version DBs not working properly
            AlbertoFaenza
            Hi,

            I don't think that the different version is giving the problem.

            It looks that user SCOTT does not have access to that table in remote database.
            It seems weird if you say that IND_NAMES table does exist in the remote schema SCOTT.

            Could you run the following statements and see objects belonging to user SCOTT in remote db?
            SELECT * FROM user_objects@droplater;
            If the table exists in remote schema SCOTT it should appear in the output among the other objects owned by schema SCOTT.

            Regards.
            Al

            Edited by: Alberto Faenza on Nov 21, 2012 2:55 PM