DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Database link to itself

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    Database link to itself

    I want to create a database link that points to itself - basically the table is moved from the remote database to another schema in the local database.

    We do not want to change the code but to create a dblink to the new schema.

    Is it possible?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    A robust implementation of database links would probably have been to apply synonyms to the remote tables, thus making the code more flexible.

    But assuming that this is not the case, I guess it is possible. Why don't you just try it?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Yes i am sure you can do it. Our programmers were using the same.

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    There is a note on implementation here...

    http://www.jlcomp.demon.co.uk/user_o...tabase%20links

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by newbie5
    I want to create a database link that points to itself - basically the table is moved from the remote database to another schema in the local database.

    We do not want to change the code but to create a dblink to the new schema.
    Why not just use a synonym? If USERA.myTable has moved to USERB.myTable put a synonym in USERA's schema named myTable and let that point to USERB.myTable.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    Thanks for the replies guys. They are very helpful.

    Marist89 - synonym won't work because we have code that has an embedded database link in it which we do not want to change.

    The copied table is in user B's schema while the code is being executed as user A. If there were a database link between user A and user B with the same name as the remote database link, the code need not be changed.

    PS: I found that loopbank links need an additional predicate at the end such as table@dblink@predicate. Probably my plan won't work.

  7. #7
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    Quote Originally Posted by newbie5
    Thanks for the replies guys. They are very helpful.

    Marist89 - synonym won't work because we have code that has an embedded database link in it which we do not want to change.

    The copied table is in user B's schema while the code is being executed as user A. If there were a database link between user A and user B with the same name as the remote database link, the code need not be changed.

    PS: I found that loopbank links need an additional predicate at the end such as table@dblink@predicate. Probably my plan won't work.
    We have an application that the vendor insisted on using dblinks (they assume a multi-server environment.) What we found was that all of the dblinks to itself are reaping havoc on the buffer cache and the sql net and dblink waits are astronomical!

    For loopback, did you put the loopback ip into tnsnames.ora in place of the hostname ?

  8. #8
    Join Date
    Jul 2000
    Posts
    521
    Code:
    SQL> grant dba to A identified by A1;
    
    Grant succeeded.
    
    SQL> grant dba to B identified by B1;
    
    Grant succeeded.
    
    SQL> conn A/A1
    Connected.
    SQL> create table t (i number);
    
    Table created.
    
    SQL> insert into t values (1);
    
    1 row created.
    
    SQL> grant all on t to B;
    
    Grant succeeded.
    
    SQL> conn B/B1
    Connected.
    SQL> create database link abc connect to A identified by A1 using 'SVK01';
    
    Database link created.
    
    SQL> select * from t@abc;
    
             I
    ----------
             1
    
    SQL>
    svk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width