DB 링크 이관을 위한 SQL-PLUS 스크립트 추출 SQL문

(운영/개발DB 암호가 다른 경우와 같이) 비밀번호가 다른 DB로 이관 작업 시,

결과로 나온 SQL문에 "" 부분을 실제 암호를 넣어 실행할 수 있도록 만든 SQL문입니다.

WITH excl AS (SELECT 'PUBLIC' username FROM DUAL UNION ALL SELECT 'SYS' username FROM DUAL)
SELECT 'CONN /AS SYSDBA' sql FROM DUAL
UNION ALL
SELECT NULL FROM DUAL
UNION ALL
SELECT * FROM (
    SELECT 'DROP ' || DECODE (OWNER, 'PUBLIC', 'PUBLIC ') || 'DATABASE LINK ' || DB_LINK || ';' || CHR(13) || CHR(10) ||
           'CREATE ' || DECODE (OWNER, 'PUBLIC', 'PUBLIC ') || 'DATABASE LINK ' || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY "" USING ''' || REPLACE(REPLACE(REPLACE(HOST, ' ', ''),CHR(13),''),CHR(10),'') || ''';' sql
    FROM DBA_DB_LINKS L
    WHERE owner = 'PUBLIC'
    ORDER BY owner
)
UNION ALL
SELECT NULL FROM DUAL
UNION ALL
SELECT * FROM (
    SELECT 'GRANT CREATE DATABASE LINK TO ' || owner ||  ';' sql FROM dba_db_links WHERE owner NOT IN (SELECT username FROM excl) AND owner NOT IN ('SYSTEM', 'TSQL') GROUP BY owner ORDER BY owner
)
UNION ALL
SELECT * FROM (
    SELECT CHR(13) || CHR(10) || 'CONN ' || owner || '/""' || CHR(13) || CHR(10) ||
           'DROP ' || DECODE (OWNER, 'PUBLIC', 'PUBLIC ') || 'DATABASE LINK ' || DB_LINK || ';' || CHR(13) || CHR(10) ||
           'CREATE ' || DECODE (OWNER, 'PUBLIC', 'PUBLIC ') || 'DATABASE LINK ' || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY "" USING ''' || REPLACE(REPLACE(REPLACE(HOST, ' ', ''),CHR(13),''),CHR(10),'') || ''';' sql
    FROM DBA_DB_LINKS L
    WHERE owner NOT IN (SELECT username FROM excl)
    ORDER BY owner
)
UNION ALL
SELECT 'CONN /AS SYSDBA' sql FROM DUAL
UNION ALL
SELECT NULL FROM DUAL
UNION ALL
SELECT * FROM (
    SELECT 'REVOKE CREATE DATABASE LINK FROM ' || owner ||  ';' sql FROM dba_db_links WHERE owner NOT IN (SELECT username FROM excl) AND owner NOT IN ('SYSTEM', 'TSQL') GROUP BY owner
)