Friday, March 23, 2012

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>

No comments: