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
Wonderful post thank you for sharing.
Hey There. I found your blog using msn. This is a really well written article.I will make sure to bookmark it and return to read more of your useful information. Thanks for the post.I’ll certainly return.
Very nice design and style and fantastic subject matter, very little else we want : D.