DB Links

Reverse Engineer DB Link


set long 99999 lines 100 

col ddl format a100 

select dbms_metadata.get_ddl('DB_LINK', 'RD_DBRI.WORLD', 'PUBLIC') as ddl from dual;

List DB Links


set lines 300 

clear column 

col owner form a12 

col host form a15 

col db_link form a20 

col username form a15 

select  OWNER, DB_LINK, USERNAME, HOST, CREATED  

from dba_db_links 

--where db_link= 'RD_DB2.WORLD';

Create DB Link


 CREATE PUBLIC DATABASE LINK "LINK1" CONNECT TO "SCOTT" IDENTIFIED BY tiger USING 'dbname';

Alter DB Link

Change password of user SCOTT on remote DB


	alter public database link RD_DEMO connect to SCOTT identified by tiger;

Version 10g

	DROP PUBLIC DATABASE LINK ;
    CREATE PUBLIC DATABASE LINK  CONNECT TO scott IDENTIFIED BY tiger USING 'ORCL'

Test all DB Links on Database


   select 'select sysdate from dual@'||db_link||';'||chr(10) from dba_db_links;