Test DB Links

Here is a small script that I recently used to quickly test all database links on various databases. DBA privileges are needed to run it.

PUBLIC database links are easy to test. Simply run a

select * from dual@dblinkname

PRIVATE database links can not be tested easily with another user, not even with dba privileges. You will get an ora error message:

 ORA-02019: connection description for remote database not found

The workaround is to create a little wrapper function “dblinktester” in the target schema.

This select fetches the needed data from the data dictionary. The column testsql can be copied and run as a script in Oracle SQL Developer or SQL*PLUS to quickly test and see if all the DB links are working or not.


select db.DB_UNIQUE_NAME, li.owner, li.db_link, li.username, li.host
    ,'prompt Link='||li.db_link||';' ||chr(10)
    || case when owner = 'PUBLIC' then -- Public link -> do simple select
         ' select ''ok'' from dual@'||li.db_link||';'
       else -- non public link -> must be run as the link owner
  'alter session set current_schema = '||owner||';'  ||chr(10)
||'create or replace function dblinktester return varchar2 is '||chr(10)
||'  v_result varchar2(5);'||chr(10)
||'   select ''ok'' into v_result from dual@'||li.db_link||';'||chr(10)
||'   return v_result; '||chr(10)
||'select dblinktester as result from dual;'||chr(10)
||'drop function dblinktester;'||chr(10)
       end as testsql
from v$database db
cross join dba_db_links li;


alter session set current_schema = MYSCHEMA;
create or replace function dblinktester return varchar2 is
v_result varchar2(5);
select ‘ok’ into v_result from dual@REMOTEDB.WORLD;
return v_result;

Example results

session SET geändert.

function DBLINKTESTER gelöscht.

Hint: replace all ” signs first, if copied with CTRL+C from SQL Developer

One thought on “Test DB Links

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.