Way to find out db link usage by database objects :
- Below query can be used to find all DB links in oracle database
SQL> select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------- --------- PUBLIC TESTDB.WORLD LINKUSER testdb 24-MAY-11
- Find DB link usage in synonyms
SQL> SELECT * FROM dba_synonyms WHERE TABLE_OWNER NOT IN('SYSTEM','SYS') AND DB_LINK IS NOT NULL; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- PRODUSER COUNTS APPDEV COUNTS TESTDB.WORLD PRODUSER COUNTS_S APPDEV COUNTS_S TESTDB.WORLD
- DB link usage in VIEWS
Oracle internal view ‘dba_views.text‘ column contains view ddl. Below pl/sql code is helpful in finding views which use this DB link.
declare cursor text is select text,view_name from dba_views where owner not in('SYS','SYSTEM'); v clob; vn varchar2(30); begin for c in text loop v:=c.text; vn:=c.view_name; if ( v like '%TESTDB.WORLD%') then dbms_output.put_line( 'view is '||vn); end if; end loop; end;
- DB link usage by PROCEDURES or FUNCTIONS:
We can verify db link usage in any procedures or package body using data dictionary view dba_source.
SQL> select OWNER,NAME,TYPE from dba_source where TEXT like '%@TESTDB%' or TEXT like '%@testdb%'; OWNER NAME TYPE ------------------------------ ------------------------------ ------------ KHAN ACTIVITY_PULL_TESTDB PACKAGE BODY