Friday, 29 January 2016

Database Links in Oracle

A database link is a schema object in one database that enables you to access objects on another database.

SQL> desc dba_db_links
 Nombre                                                                                     ¨Nulo?   Tipo
 ------------------------------------------------------------------------------------------ -------- -------------------------------------------------------------
 OWNER                                                                                      NOT NULL VARCHAR2(30)
 DB_LINK                                                                                    NOT NULL VARCHAR2(128)
 USERNAME                                                                                            VARCHAR2(30)
 HOST                                                                                                VARCHAR2(2000)
 CREATED                                                                                    NOT NULL DATE

Use the below query to find the db links available for a database:

set lines 152
col owner for a15
col db_link for a30
col username for a15
col host for a30

select owner,db_link,username,host,CREATED
from dba_db_links;

spool db_link_test.sql

select 'select count(*) from tab@'||db_link||';'
from dba_db_links;

spool off

spool db_link_output.log


spool off


How to Test if a DB link is working fine?

select ' connect '||owner||'/'||owner||'1'||chr(10)||
'select count(*) from tab@'||db_link||';'
from dba_db_links;

select distinct owner
from dba_db_links;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer
    LinkedIn profile -
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.


Was this Post Helpful?

Feel free to suggest your opinions in the comments section!