Thursday, March 26, 2009

recreate database links using DBMS_METADATA

SQL> set serveroutput on size 1000000 long 1000000
SQL> SELECT dbms_metadata.get_ddl('DB_LINK','TEST_LINK','OLD_USER') stmt FROM dual

STMT
--------------------------------------------------------------------------------

CREATE DATABASE LINK "TEST_LINK"
CONNECT TO "TEST" IDENTIFIED BY VALUES '05A6A92EE87313FA69F1EC1C5D29512A48'
USING 'TEST'



SQL> connect newuser/password
Connected.
SQL> CREATE DATABASE LINK "TEST_LINK"
CONNECT TO "TEST" IDENTIFIED BY VALUES '05A6A92EE87313FA69F1EC1C5D29512A48'
USING 'TEST' 2 3 ;

Database link created.

SQL> select sysdate from dual@test_link;

SYSDATE
--------------------
26 MAR 2009 15:37:31