4 Replies Latest reply: May 17, 2012 6:41 PM by User347578 RSS

    request for help in acessing a schema objects from another user

    User347578
      Hi

      I am not sure of the procedure how to acess schema objects from one database to another database which are on two different servers .


      for example i have a database abc and an user a on server qss would like to access all the schema objects of an user/schema exc on a database xyz on server pss


      which obviously has the network connection established and able to ping the server .


      Any suggestion on this is highly apprciated .

      thanks in advance
        • 1. Re: request for help in acessing a schema objects from another user
          AliD
          You create a Database link: http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm
          • 2. Re: request for help in acessing a schema objects from another user
            sb92075
            790072 wrote:
            Hi

            I am not sure of the procedure how to acess schema objects from one database to another database which are on two different servers .


            for example i have a database abc and an user a on server qss would like to access all the schema objects of an user/schema exc on a database xyz on server pss


            which obviously has the network connection established and able to ping the server .


            Any suggestion on this is highly apprciated .
            database link

            http://www.oracle.com/pls/db111/search?word=db_link&partno=
            • 3. Re: request for help in acessing a schema objects from another user
              User347578
              Hi I tried to create a database link cmsp1 as

              create public database link cmsp1 connect to pqcxch identified by ******** using 'cmsps'

              database link created .

              The TNS entry for cmsps is as below

              cmsps.elinux.***.***.**=
              (DESCRIPTION =
              (ADDRESS =
              (COMMUNITY = tcpip.world)
              (PROTOCOL = TCP)
              (Host = vsv1.elinux.***.***.**)
              (Port = 25001)
              )
              (CONNECT_DATA =
              (SID = cmsp)
              (GLOBAL_NAME = cmsp.elinux.***.***.**)


              And the database and user from where the table is to be queried is on the server vsv1.


              cmsp SQL> select * from PQC_XCHG_CONFIG@cmsp1;

              ERROR at line 1:
              ORA-02085: database link CMSP1.ELINUX.***.***.** connects to CMSP.ELI NUX.XXX.XXX


              BUT NOT GIVING THE RESULT OF THE TABLE PQC_XCHG_CONFIG


              any suggestion on this is highly apprciated .

              Thanks in advance .

              Edited by: 790072 on 17/05/2012 16:33

              Edited by: 790072 on 17/05/2012 16:40
              • 4. Re: request for help in acessing a schema objects from another user
                sb92075
                790072 wrote:
                Hi I tried to create a database link cmsp1 as

                create public database link cmsp1 connect to pqcxch identified by ******** using 'cmsps'

                database link created .

                The TNS entry for cmsps is as below

                cmsps.elinux.***.***.**=
                (DESCRIPTION =
                (ADDRESS =
                (COMMUNITY = tcpip.world)
                (PROTOCOL = TCP)
                (Host = vsvdbsp01.elinux.***.***.**)
                (Port = 25001)
                )
                (CONNECT_DATA =
                (SID = cmsp)
                (GLOBAL_NAME = cmsp.elinux.***.***.**)


                And the database and user from where the table is to be queried is on the server vsvdbsp01.


                cmsp SQL> select * from PQC_XCHG_CONFIG@cmsp1;

                ERROR at line 1:
                ORA-02085: database link CMSP1.ELINUX.***.***.** connects to CMSP.ELI NUX.XXX.XXX


                BUT NOT GIVING THE RESULT OF THE TABLE PQC_XCHG_CONFIG


                any suggestion on this is highly apprciated .

                Thanks in advance .

                Edited by: 790072 on 17/05/2012 16:33
                02085, 00000, "database link %s connects to %s"
                // *Cause: a database link connected to a database with a different name.
                //  The connection is rejected.
                // *Action: create a database link with the same name as the database it
                //  connects to, or set global_names=false.
                //