Month: December 2016

Oracle db link usage

Goutham 3 Comments

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