Here is a PL\/SQL script to validate the database links accessible to an user. \u00a0I wrote this to identify the working links after a database refresh or to verify links that connect from non-prod to prod…<\/p>\n
Hope it helps…<\/p>\n
<\/p>\n
REM Validate Database Links\nREM Private links under connected user and Public links\nREM\nREM Biju Thomas - 29-Oct-2013\nREM\nset serveroutput on size 99999\nset pages 0 lines 300 trims on\ncol spoolfile new_value spoolfname\nselect '\/tmp\/checklinks_'||user ||'_'||\n substr(global_name, 1, instr(global_name,'.')-1)||\n '.txt' spoolfile from global_name;\nspool '&spoolfname'\ndeclare\n --\n -- Get list of links the user has access to\n cursor mylinks is select db_link, owner, created, host, username\n from all_db_links;\n --\n -- Identify other links in the DB for information\n cursor otherlinks is select db_link, owner\n from dba_db_links\n minus\n select db_link, owner\n from all_db_links;\n dbname varchar2 (200);\n currentuser varchar2 (30);\n linkno number := 0;\nbegin\n\n -- Current database and connected user\n select name, user into dbname, currentuser from v$database;\n dbms_output.put_line('Verifying Database Links '||currentuser||'@'||dbname);\n dbms_output.put_line('========================================================');\n --\n for linkcur in mylinks loop\n linkno := linkno + 1;\n dbms_output.put_line('Checking Link: ' || linkno) ;\n dbms_output.put_line('Link Name : ' || linkcur.db_link) ;\n dbms_output.put_line('Link Owner : ' || linkcur.owner) ;\n dbms_output.put_line('Connect User : ' || linkcur.username) ;\n dbms_output.put_line('Connect To : ' || linkcur.host) ;\n begin\n --\n -- Connect to the link to validate, get global name of destination database\n execute immediate 'select global_name from global_name@\"'||linkcur.db_link||'\"' into dbname;\n dbms_output.put_line('$$$$ DB LINK SUCCESSFULLY connected to '||dbname);\n --\n -- end the transaction and explicitly close the db link\n commit;\n execute immediate 'alter session close database link \"'||linkcur.db_link||'\"';\n exception\n --\n -- DB Link connection failed, show error message\n when others then\n dbms_output.put_line('@@@@ DB LINK FAILED @@@@');\n dbms_output.put_line('Error: '||sqlerrm);\n end;\n dbms_output.put_line('---------------------------------------');\n dbms_output.put_line(' ');\n end loop;\n dbms_output.put_line('Tests Completed.');\n --\n -- List other Links in the DB\n dbms_output.put_line('Other Private Links in the Database');\n dbms_output.put_line('Connect as respective owner to validate these.');\n dbms_output.put_line('----------------------------------------------');\n for olinks in otherlinks loop\n dbms_output.put_line(olinks.owner ||' :: '||olinks.db_link);\n end loop;\nend;\n\/\n\nspool\nspool off\nset pages 99 lines 80 trims off<\/pre>\n <\/p>\n
Sample Output:<\/p>\n
Verifying Database Links APPS@TST\n========================================================\nChecking Link: 1\nLink Name : CL.FE.NET\nLink Owner : PUBLIC\nConnect User : M_S_DBLINK\nConnect To : prod\n@@@@ DB LINK FAILED @@@@\nError: ORA-28000: the account is locked\nORA-02063: preceding line from CL\n---------------------------------------\nChecking Link: 2\n\n...\n...\n...\n\n---------------------------------------\nChecking Link: 28\nLink Name : EC1.E.NET\nLink Owner : APPS\nConnect User : QUERY\nConnect 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)))\n$$$$ DB LINK SUCCESSFULLY connected to PROD\n---------------------------------------\nTests Completed.\nOther Private Links in the Database\nConnect as respective owner to validate these.\n----------------------------------------------\nSYSTEM :: CPROD.FE.NET\nFQUERY :: GPROD2.FE.NET\nFQUERY :: GASPRD.FE.NET\nFQUERY :: PPROD.FE.NET\nXXG :: PPROD.FE.NET\nFQUERY :: SPROD.FE.NET\n\nPL\/SQL procedure successfully completed.\n\ncurrently spooling to \/tmp\/checklinks_APPS_TST.txt<\/pre>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Here is a PL\/SQL script to validate the database links accessible to an user. \u00a0I 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 … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[],"class_list":["post-222","post","type-post","status-publish","format-standard","hentry","category-oracledb"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/222","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=222"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/222\/revisions"}],"predecessor-version":[{"id":3000,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/222\/revisions\/3000"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=222"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}