CODEWITHSUNDEEP
oracle-databaseviewdblink
coeurdange57
coeurdange57

Reputation: 743

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

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

Upvotes: 1

Views: 14545

Answers (3)

Littlefoot
Littlefoot

Reputation: 143088

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>

Upvotes: 2

Matthew McPeak
Matthew McPeak

Reputation: 17944

To add to the other (correct) answers that have been posted by @Littlefoot and @EdStevens, a quick-and-dirty analysis can also be made against the Automated Workload Repository (AWR).

The benefit of this approach is it will find usages of remote objects from SQL submitted to the database whether that SQL is in DBA_SOURCE or not (e.g., is embedded in an external application).

-- Find any objects referenced across a database link (sort of)
select object_node, object_name, count(distinct sql_id) sql_id_count
from dba_hist_sql_plan
where object_type = 'REMOTE'
group by object_node, object_name
order by object_node, object_name
;

The problem is that AWR data isn't 100% complete. First of all, it's not kept around forever, so a database link last used more than a month (or two months or however long your DBAs keep AWR data for) wouldn't be seen. Second of all, AWR only takes snapshots periodically, say every hour. So it's theoretically possible for a SQL to use a database link and then get aged out of the library cache before the next AWR snapshot.

I think the chance of missing something due to that last bit is small on the systems I work with. But, if you have poorly written applications (i.e., no bind variables) and limited shared pool space, it's something to worry about.

Upvotes: 1

EdStevens
EdStevens

Reputation: 3872

There is no complete answer. How would the database know of code that is outside of the database? It can't. So if you have a sql script, or some application that does not rely on stored procedures to do everything, the database will not know of them.

That said, for dependencies that are in stored procedures in the database, you can try this:

select *
from dba_dependencies
where referenced_link_name is not null
;

Upvotes: 1

Related Questions