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