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
Determining Which Link Connections Are Open

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