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