Viewing Information About Database Links - Oracle 10g

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:

Determining Which Links Are in the Database

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

Determining Which Links Are in the Database

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:

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

Authorization for Viewing Password Information

Only USER _DB _LINKS contains a column for password information. However, if you are an administrative user (SYS or users who connect AS SYSDBA), then you can view passwords for all links in the database by querying the LINK$ table. If you are not an administrative user, you can be authorized to query the LINK$ table by one of the following methods:

  • Being granted specific object privilege for the LINK$ table
  • Being granted the SELECT ANY DICTIONARY system privilege

Viewing Password Information

You can create and run the following script in SQL*Plus to obtain password information (sample output included):

Viewing Authentication Passwords

It is possible to view AUTHENTICATED BY ... IDENTIFIED BY ... usernames and passwords for all links in the database by querying the LINK$ table. You can create and run the following script in SQL*Plus to obtain password information (sample output included):

You can also view the link and password information together in a join by creating and executing the following script (sample output included):

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:

Determining Which Link Connections Are Open

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

Determining-Which-Link-Connections1

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

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics