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.

SQL

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)
||'begin'||chr(10)
||'   select ''ok'' into v_result from dual@'||li.db_link||';'||chr(10)
||'   return v_result; '||chr(10)
||'end;'||chr(10)
||'/'||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;

Example

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

Example results

Link=REMOTEDB.WORLD
session SET geändert.
FUNCTION DBLINKTESTER kompiliert
DBLINKTESTER
———-
ok

function DBLINKTESTER gelöscht.

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

Advertisements