• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1914
  • Last Modified:

How To Determine Where DB Links Are Used

Greetings, experts!

We have some Oracle 7.x and 8i databases that are currently using DB links to another database somewhere in its code. What's the easiest way to find out where the DB link is referenced in the code? For example, if the DB link is called "RPT", I don't want to manually have to search through every function, procedure, package body, etc. to locate any instances of "RPT". I thought I could search the database where the code is stored directly. By the way, they use Embarcadero's RapidSQL here, and not OEM.

2 Solutions
jdritsAuthor Commented:
Update: We also use SQL*PLUS here, of course. That is where I am thinking I will do the query. Any help is greatly appreciated!
select * from V$DBLINK;
This view describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be committed or rolled back before being closed.

Column Datatype Description
DB_LINK VARCHAR2(128) Name of the database link
OWNER_ID NUMBER Owner of the database link UID
LOGGED_ON VARCHAR2(3) Whether the database link is currently logged on
HETEROGENEOUS VARCHAR2(3) Whether the database link is heterogeneous
PROTOCOL VARCHAR2(6) Communication protocol for the database link
OPEN_CURSORS NUMBER Whether there are open cursors for the database link
IN_TRANSACTION VARCHAR2(3) Whether the database link is currently in a transaction
UPDATE_SENT VARCHAR2(3) Whether there has been an update on the database link
COMMIT_POINT_STRENGTH NUMBER Commit point strength of the transactions on the database link
GV$DBLINK will add The instance from which the view information was obtained.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

jdritsAuthor Commented:
So when I do this query, the IN_TRANSACTION will tell me if a transaction is engaged for that DB link AT THAT TIME? That is indeed useful to know, but a lot of our DB Links are run from code that is only executed in the middle of the night.

That is why I wanted to search through the code to see if the DB link is referred to anywhere.
jdritsAuthor Commented:
Also it tells me:

SQL> select * from V$DBLINK;
select * from V$DBLINK
ERROR at line 1:
ORA-00942: table or view does not exist

So I probably don't have permissions to view it.
V$DBLINK is a view for DBAs only
jdritsAuthor Commented:
Ok, is there a way to search through the code for a string?
OBJECT_NODE_PLUS_EXP     Shows the database links or parallel query servers used.

Execution Plan
The Execution Plan shows the SQL optimizer's query execution path.
Each line of the Execution Plan has a sequential line number. SQL*Plus also displays the line number of the parent operation.

The Execution Plan consists of four columns displayed in the following order:

Column Name              Description
ID_PLUS_EXP              Shows the line number of each execution step.
PARENT_ID_PLUS_EXP       Shows the relationship between each step and its parent.  This column is useful for large reports.
PLAN_PLUS_EXP            Shows each step of the report.
OBJECT_NODE_PLUS_EXP     Shows the database links or parallel query servers used.
try this:
select distinct type,name from dba_source where upper(text) like '%RPT%'
and owner not in ('SYS','SYSTEM');

you could refine your search with:
select distinct type,name from dba_source where upper(text) like '%@RPT%'
and owner not in ('SYS','SYSTEM');

if you want to search only one schema replace "and owner not in ('SYS','SYSTEM')" with and owner = 'Your Schema"
Well, You could use the method mentioned by KurtR to identify the name/type of object that has RPT in the source code.
You could select from ALL_SOURCE also (in case you don't have access to DBA_SOURCE).

Some important things :
1. DB Links are used with an @ sign, For example you may have 'SELECT * FROM TABLEA@RPT' in the code. So you may check for the codes that have '@RPT' in the source.
e.g. : select distinct name, type, owner from all_source where upper(text) like '%@RPT%';

2. Check if public or private synonyms are defined for the tables that are being accessed through the DB Links. In this case, you will have to search for the SYNONYM names.
e.g. : select synonym_name from all_synonyms where db_link like '%RPT%';
After identifying all the synonyms, select the names/types from the all_source that have the synonyms that you have identified by the above statements.

These are the views you can refer to, to identify the above cases :
SQL> desc all_source
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL> desc all_db_links
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DB_LINK                                   NOT NULL VARCHAR2(128)
 USERNAME                                           VARCHAR2(30)
 HOST                                               VARCHAR2(2000)
 CREATED                                   NOT NULL DATE

SQL> desc all_synonyms
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 SYNONYM_NAME                              NOT NULL VARCHAR2(30)
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 DB_LINK                                            VARCHAR2(128)

Hope this helps !
jdritsAuthor Commented:
Thanks folks! I will try these when i get back into the office tomorrow.
jdritsAuthor Commented:
Thanks folks! Worked perfectly.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now