Database link to itself

[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.

Is it possible?

[slimdave]

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?

[paragp1981]

Yes i am sure you can do it. Our programmers were using the same.

[padders]

There is a note on implementation here...

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

[marist89]

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.

[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.

[dbtoo]

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 ?

[svk]

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>