How to know if a db-link is used somewhere in a DB

Question

I would like to know if it exists an Oracle command to know if a DB-LINK (name: myBDLink) is used somewhere in a DB and how to display the objects (views,materialized views, procedures, functions, ...) which use it.

Could you please help me with that ?

Thanks for your help

Answer 1

Well, you could try to query various system views and see whether any of them contains a string you're looking for. As you want to check the whole database, you'll probably connect as a privileged user and check dba_ views; otherwise, all_ or user_ would do.

For example, to check procedures, functions, packages, ...:

select owner, name, type, line
from dba_source
where owner not in ('SYS', 'SYSTEM')
  and lower(text) like '%mydblink%';

The PUBLIC.ALL_SOURCE or DBA_SOURCE covers typically all the object types and their contents, namely PACKAGE BODY, TRIGGER, PACKAGE, PROCEDURE, FUNCTION and TYPE. But unfortunately, it doesn't cover SYNONYMS, and that exactly where DB_LINKS are used extensively.

Hence to check synonyms,

select * from all_synonyms where db_link = 'MYDBLINKNAME';

To check views, you'll need a function which will search through its LONG datatype column (as you can't use it directly in SQL):

create or replace function f_long(par_view in varchar2, par_String in varchar2) 
  return varchar2 
is
  l_text varchar2(32000);
begin
  select text 
    into l_text
    from dba_views 
    where owner not in ('SYS', 'SYSTEM')
      and view_name = par_view;
    
  return case when instr(lower(l_text), lower(par_string)) > 0 then 1
              else 0 
         end;              
end;
/

and then

select owner, view_name 
from dba_views
where f_long(view_name, 'mydblink') = 1;

---

I excluded SYS and SYSTEM as they should not contain anything of users' stuff. Perhaps you'd want to exclude some more users.

---

To see some more (re)sources, query the dictionary, e.g.

select table_name, comments
from dictionary;

TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------
USER_CONS_COLUMNS              Information about accessible columns in constraint
                                definitions

ALL_CONS_COLUMNS               Information about accessible columns in constraint
                                definitions

DBA_CONS_COLUMNS               Information about accessible columns in constraint
                                definitions
<snip>