DB Link 일괄 삭제

Owner 로 DB Link 검증 및 삭제 

** Sys, system 은 안됨

select  OWNER, DB_LINK, USERNAME, HOST, CREATED
        ,DECODE(OWNER
                -- Public DB Link
                ,'PUBLIC'
                ,' SELECT GLOBAL_NAME FROM GLOBAL_NAME@'||DB_LINK||';'||chr(13)||
                 'drop public database link '||db_link||';',
                -- Private DB Link 
                '-- step 1.1'||chr(13)||
                -- Private DB 작동 여부 파악 
                -- 아래 평션 생성 시, 실패 DB LINK 수행 되면 컴파일 됨 >> 미사용시, 컴파일 시간이 오래 걸림 
                'CREATE OR REPLAACE FUNCTION '||owner||'.'||db_link||'_CHK '||chr(13)||                
                'RETURN VARCHAR'||CHR(13)||
                'IS '||CHR(13)||
                'V_NAME VARCHAR2(30);'||chr(13)||
                'BEGIN'||chr(13)||
                'SELECT GLOBAL_NAME INTO V_NAME FROM  GLOBAL_NAME@'||DB_LINK||';'||chr(13)||
                ' RETURN V_NAME ;'||CHR(13)||
                'end ; '||chr(13)||
                '/ '||CHR(13)||
                '-- step 1.2'||chr(13)||
                'SELECT '||owner||'.'||db_link||'_chk FROM DUAL ;'||CHR(13)||
                '-- step 1.3'||chr(13)||
                'DROP FUNCTION '||owner||'.'||db_link||'_CHK ; '||chr(13)||                       
                
                -- Private DB Link 작동 않될 경우 >> 삭제 
                '--step 2.1'||chr(13)||
                'CREATE OR REPLACE PROCEDURE '||owner||'.DROP_DB_LINK as'||chr(13)||
                'BEGIN '||chr(13)||
                'EXECUTE IMMEDIATE ''DROP DATABASE LINK '||db_link||''';'||chr(13)||
                'END ; '||chr(13)||
                '/ '||chr(13)||
                '--step 2.2'||chr(13)||
                'EXECUTE '||owner||'.DROP_DB_LINK;'||chr(13)||
                '--step 2.3'||chr(13)||
                'DROP PROCEDURE '||owner||'.DROP_DB_LINK;'||chr(13) 
               ) AS DB_LINK_SQL
from dba_db_links
where 1=1
AND   OWNER <>'SYS'
ORDER BY OWNER, DB_LINK 
;