Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E10595-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Viewing Information About Database Links

The data dictionary of each database stores the definitions of all the database links in the database. You can use data dictionary tables and views to gain information about the links. This section contains the following topics:

Determining Which Links Are in the Database

The following views show the database links that have been defined at the local database and stored in the data dictionary:

View Purpose
DBA_DB_LINKS Lists all database links in the database.
ALL_DB_LINKS Lists all database links accessible to the connected user.
USER_DB_LINKS Lists all database links owned by the connected user.

These data dictionary views contain the same basic information about database links, with some exceptions:

Column Which Views? Description
OWNER All except USER_* The user who created the database link. If the link is public, then the user is listed as PUBLIC.
DB_LINK All The name of the database link.
USERNAME All If the link definition includes a fixed user, then this column displays the username of the fixed user. If there is no fixed user, the column is NULL.
PASSWORD Only USER_* Not used. Maintained for backward compatibility only.
HOST All The net service name used to connect to the remote database.
CREATED All Creation time of the database link.

Any user can query USER_DB_LINKS to determine which database links are available to that user. Only those with additional privileges can use the ALL_DB_LINKS or DBA_DB_LINKS view.

The following script queries the DBA_DB_LINKS view to access link information:

COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/

Here, the script is invoked and the resulting output is shown:

SQL>@link_script

OWNER      DB_LINK                        USER     SERVICE CREATED
---------- ------------------------------ -------- ------- ----------
SYS        TARGET.US.ACME.COM             SYS      inst1   23-JUN-99
PUBLIC     DBL1.UK.ACME.COM               BLAKE    ora51   23-JUN-99
PUBLIC     RMAN2.US.ACME.COM                       inst2   23-JUN-99
PUBLIC     DEPT.US.ACME.COM                        inst2   23-JUN-99
JANE       DBL.UK.ACME.COM                BLAKE    ora51   23-JUN-99
SCOTT      EMP.US.ACME.COM                SCOTT    inst2   23-JUN-99
6 rows selected.

Determining Which Link Connections Are Open

You may find it useful to determine which database link connections are currently open in your session. Note that if you connect as SYSDBA, you cannot query a view to determine all the links open for all sessions; you can only access the link information in the session within which you are working.

The following views show the database link connections that are currently open in your current session:

View Purpose
V$DBLINK Lists all open database links in your session, that is, all database links with the IN_TRANSACTION column set to YES.
GV$DBLINK Lists all open database links in your session along with their corresponding instances. This view is useful in an Oracle Real Application Clusters configuration.

These data dictionary views contain the same basic information about database links, with one exception:

Column Which Views? Description
DB_LINK All The name of the database link.
OWNER_ID All The owner of the database link.
LOGGED_ON All Whether the database link is currently logged on.
HETEROGENEOUS All Whether the database link is homogeneous (NO) or heterogeneous (YES).
PROTOCOL All The communication protocol for the database link.
OPEN_CURSORS All Whether cursors are open for the database link.
IN_TRANSACTION All Whether the database link is accessed in a transaction that has not yet been committed or rolled back.
UPDATE_SENT All Whether there was an update on the database link.
COMMIT_POINT_STRENGTH All The commit point strength of the transactions using the database link.
INST_ID GV$DBLINK only The instance from which the view information was obtained.

For example, you can create and execute the script below to determine which links are open (sample output included):

COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK
/

SQL> @dblink

DB_LINK                   OWNID LOGON HETER PROTOCOL OPN_CUR TXN UPDATE  C_P_S
------------------------- ------ ----- ----- -------- ------- --- ------ ------
INST2.ACME.COM                 0 YES   YES    UNKN          0 YES YES       255