How to Test a Database Link

Before creating a database link, you need a schema to connect to in the remote database:

 

SQL> conn /@remotedb

Connected.

SQL> grant create session to link_schema

  2  identified by link_schema_password

  3  /

 

Grant succeeded.

 

SQL>

 

Then you can create a database link in the local database as follows:

 

SQL> create database link andrews_link

  2  connect to link_schema

  3  identified by link_schema_password

  4  using 'REMOTEDB'

  5  /

 

Database link created.

 

SQL>

 

... and you can test it like this:

 

SQL> select * from dual@andrews_link

  2  /

 

D

-

X

 

SQL>

 

If things go wrong in the remote database, the test will fail in the local database and will often give you a good idea of what is wrong. Here is one example:

 

SQL> conn /@remotedb

Connected.

SQL> revoke create session from link_schema

  2  /

 

Revoke succeeded.

 

SQL> conn /@localdb

Connected.

SQL> select * from dual@andrews_link

  2  /

select * from dual@andrews_link

                   *

ERROR at line 1:

ORA-01045: user LINK_SCHEMA lacks CREATE SESSION privilege; logon denied

ORA-02063: preceding line from ANDREWS_LINK

 

SQL>

 

... and here is another:

 

SQL> conn /@remotedb

Connected.

SQL> alter user link_schema

  2  identified by new_password

  3  /

 

User altered.

 

SQL> conn /@localdb

Connected.

SQL> select * from dual@andrews_link

  2  /

select * from dual@andrews_link

                   *

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from ANDREWS_LINK

 

SQL>