This discussion is archived
4 Replies Latest reply: May 17, 2012 4:41 PM by User347578 RSS

request for help in acessing a schema objects from another user

User347578 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
    //

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points