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