Author: Biju

Here is a PL/SQL script to validate the database links accessible to an user.  I wrote this to identify the working links after a database refresh or to verify links that connect from non-prod to prod…

Hope it helps…

REM Validate Database Links
REM Private links under connected user and Public links
REM
REM Biju Thomas - 29-Oct-2013
REM
set serveroutput on size 99999
set pages 0 lines 300 trims on
col spoolfile new_value spoolfname
select '/tmp/checklinks_'||user ||'_'||
       substr(global_name, 1, instr(global_name,'.')-1)||
       '.txt' spoolfile from global_name;
spool '&spoolfname'
declare
 --
 -- Get list of links the user has access to
 cursor mylinks is select db_link, owner, created, host, username
                   from all_db_links;
 --
 -- Identify other links in the DB for information
 cursor otherlinks is select db_link, owner
                      from dba_db_links
                      minus
                      select db_link, owner
                      from all_db_links;
 dbname varchar2 (200);
 currentuser varchar2 (30);
 linkno number := 0;
begin

 -- Current database and connected user
 select name, user into dbname, currentuser from v$database;
 dbms_output.put_line('Verifying Database Links '||currentuser||'@'||dbname);
 dbms_output.put_line('========================================================');
 --
 for linkcur in mylinks loop
  linkno := linkno + 1;
  dbms_output.put_line('Checking Link: ' || linkno) ;
  dbms_output.put_line('Link Name    : ' || linkcur.db_link) ;
  dbms_output.put_line('Link Owner   : ' || linkcur.owner) ;
  dbms_output.put_line('Connect User : ' || linkcur.username) ;
  dbms_output.put_line('Connect To   : ' || linkcur.host) ;
  begin
    --
    -- Connect to the link to validate, get global name of destination database
    execute immediate 'select global_name from global_name@"'||linkcur.db_link||'"' into dbname;
    dbms_output.put_line('$$$$ DB LINK SUCCESSFULLY connected to '||dbname);
    --
    -- end the transaction and explicitly close the db link
    commit;
    execute immediate 'alter session close database link "'||linkcur.db_link||'"';
  exception
    --
    -- DB Link connection failed, show error message
    when others then
    dbms_output.put_line('@@@@ DB LINK FAILED  @@@@');
    dbms_output.put_line('Error: '||sqlerrm);
  end;
  dbms_output.put_line('---------------------------------------');
  dbms_output.put_line(' ');
 end loop;
 dbms_output.put_line('Tests Completed.');
 --
 -- List other Links in the DB
 dbms_output.put_line('Other Private Links in the Database');
 dbms_output.put_line('Connect as respective owner to validate these.');
 dbms_output.put_line('----------------------------------------------');
 for olinks in otherlinks loop
   dbms_output.put_line(olinks.owner ||' :: '||olinks.db_link);
 end loop;
end;
/

spool
spool off
set pages 99 lines 80 trims off

Sample Output:

Verifying Database Links APPS@TST
========================================================
Checking Link: 1
Link Name    : CL.FE.NET
Link Owner   : PUBLIC
Connect User : M_S_DBLINK
Connect To   : prod
@@@@ DB LINK FAILED  @@@@
Error: ORA-28000: the account is locked
ORA-02063: preceding line from CL
---------------------------------------
Checking Link: 2

...
...
...

---------------------------------------
Checking Link: 28
Link Name    : EC1.E.NET
Link Owner   : APPS
Connect User : QUERY
Connect To   : (DESCRIPTION=(FAILOVER=yes)(LOAD_BALANCE=yes)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=B902A-VIP.e.net)(PORT=1532))(ADDRESS=(PROTOCOL=TCP)(HOST=B902B-VIP.e.net)(PORT=1532)))(CONNECT_DATA=(SERVICE_NAME=PROD)))
$$$$ DB LINK SUCCESSFULLY connected to PROD
---------------------------------------
Tests Completed.
Other Private Links in the Database
Connect as respective owner to validate these.
----------------------------------------------
SYSTEM :: CPROD.FE.NET
FQUERY :: GPROD2.FE.NET
FQUERY :: GASPRD.FE.NET
FQUERY :: PPROD.FE.NET
XXG :: PPROD.FE.NET
FQUERY :: SPROD.FE.NET

PL/SQL procedure successfully completed.

currently spooling to /tmp/checklinks_APPS_TST.txt