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

@db_link_test.sql

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 Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete

Was this Post Helpful?

Feel free to suggest your opinions in the comments section!