Script to generate DDL of db-links

set lines 800
set echo off
–set feedb off
set termout off
set heading off
SET VERIFY off
set trimspool on
set long 32766
set longchunksize 120

select ‘————————–‘||owner||’—-‘||db_link||’———————————————————‘||
chr(10)||
chr(10)||
‘CREATE OR REPLACE PROCEDURE ‘||OWNER||’.drop_db_link AS ‘||
chr(10)||
‘BEGIN ‘||
chr(10)||
‘   EXECUTE IMMEDIATE ”DROP DATABASE LINK ‘||db_link || ‘ ” ‘||’;’||
chr(10)||
‘END drop_db_link ;’||
CHR(10)||
‘/’||
chr(10)||
chr(10)||
‘exec ‘||owner||’.DROP_DB_LINK;’||
CHR(10)||
‘DROP PROCEDURE ‘||OWNER||’.drop_db_link ;’||
chr(10)||
chr(10)||
‘CREATE OR REPLACE PROCEDURE ‘||OWNER||’.create_db_link as ‘ ||
chr(10)||
‘BEGIN’||
chr(10)||
‘   EXECUTE IMMEDIATE ”CREATE DATABASE LINK ‘||db_link|| ‘ ” ‘||chr(10)||’||”CONNECT TO ‘||USERNAME||’ IDENTIFIED BY xxxx ”’||
chr(10)||
‘||’||”’USING ””’||HOST||””””||’;’||
chr(10)||
‘END create_db_link;’||
chr(10)||’/’ ||
chr(10)||
chr(10)||
‘exec ‘||OWNER||’.create_db_link ;’||
chr(10)||’drop procedure ‘||OWNER||’.create_db_link ;’||
chr(10) as script
FROM dba_db_links
ORDER BY 1; 

  • July 14, 2018 | 16 views
  • Comments