View and Retrieve Oracle Database Link Details

»»»View and Retrieve Oracle Database Link Details
In Oracle, database links are named objects that are created in one database which contains detail and instruction that describes how to connect to another, entirely different, database. Database links are is a mean of performing distributed database processing, and can be used to retrieve data from a remote database of different version, or running on different operating system.

If you want to know whether any database links exist in data dictionary views, and where the database links connect to, you can get the information from DBA_DB_LINKS view.

Just launch your SQL*Plus or Toad, or whatever convenient to you and query the dba_db_links table. For example:

SQL> select * from dba_db_links;

And you will be seeing listing of database links defined, with details of OWNER, DB_LINK, USERNAME, HOST, and CREATED, which the column name itself is more or less self-explanatory.

Normally the OWNER of the database link is the person who created it, unless it was created as a public link, in which case a pseudo-user called PUBLIC is said to own it.

Beside, if you define connection host string as an entry (alias) from tnsnames.ora when creating the database links, the HOST column will show the tnsnameas alias, and not the host or remote server name that the database link is connecting to. You have to check tnsnames.ora to get the connection details.

By |2016-12-09T08:43:19+00:00December 9th, 2016|Categories: Databases|Tags: |2 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.