Database chain and synonyms

You need to pay extra attention when creating synonyms that contain database links. Otherwise, problems may occur.

If the synonym points to the object of the current user, there will be no problem:

SQL> CONN YANGTK/YANGTK @ YTK is connected. SQL> CREATE DATABASE LINK TEST2 CONNECT TO TEST IDENTIFIED BY TEST USING 'test2 '; The database link has been created. SQL> SELECT COUNT (*) FROM TEST @ TEST2; COUNT (*) ---------- 5 SQL> CREATE SYNONYM TEST FOR TEST @ TEST2; You have created a synonym. SQL> SELECT COUNT (*) FROM TEST; COUNT (*) ---------- 5 If another user creates a synonym pointing to TEST @ TEST2, the following error occurs: SQL> CREATE USER ABC IDENTIFIED BY ABC; The user has been created. SQL> GRANT DBA TO ABC; Authorization successful. SQL> CONN ABC/ABC @ YTK is connected. SQL> CREATE SYNONYM TEST FOR YANGTK. TEST; You have created a synonym. SQL> SELECT COUNT (*) FROM TEST; Select count (*) FROM TEST * Error in row 1st: ORA-02019: Description of remote database connection not found

The most fundamental cause of this problem is the database chain. The special format of the database chain determines that it cannot be accessed through SCHEMA. DBLINK_NAME. Therefore, the user can only access the database chain or PUBLIC database chain of the current user.

For synonyms, it is just an alias. In actual execution, it will still be replaced with the original name, so the actual execution is: select count (*) from test @ TEST2.

The current user does not include the database chain, so the above error occurs. At this time, the same database chain needs to be created under the current user:

SQL> CREATE DATABASE LINK TEST2 CONNECT TO TEST IDENTIFIED BY TEST USING 'test2 '; The database link has been created. SQL> SELECT COUNT (*) FROM TEST; COUNT (*) ---------- 5

This problem does not occur if the synonym points to the global database chain.

Note that if the current user already has a database chain pointing to another database with the same name, or the user creates a database chain with the same name, it will cause an error.

SQL> CREATE DATABASE LINK TEST2 CONNECT TO TEST IDENTIFIED BY TEST USING 'testdata '; The database link has been created. SQL> SELECT COUNT (*) FROM TEST; COUNT (*) ---------- 1 SQL> SELECT COUNT (*) FROM YANGTK. TEST; COUNT (*) ---------- 1 SQL> CONN YANGTK/YANGTK @ YTK is connected. SQL> SELECT COUNT (*) FROM TEST; COUNT (*) ---------- 5

On the surface, two users access the same object, but the two users access different databases in time. Be careful here.