안녕하세요
아래의 11g에서 사용하는 쿼리를 10g이하에서도 사용가능하게 하고싶은데 가능할까요?
with dblink as
(
select '--as connect '||owner||'; --//you have to connect each schema' owner
,to_char(case when owner='PUBLIC' then '--DROP '||OWNER||' DATABASE LINK '||DB_LINK||';' else '--DROP DATABASE LINK '||DB_LINK||';' end) drop_dblink
,to_char(replace(dbms_metadata.get_ddl('DB_LINK',db_link,owner),Chr(10),''))||';' creation_dblink
from dba_db_links
)
select replace(x,'"','') from dblink
unpivot ( x for y in ( owner as 'owner', drop_dblink as 'drop_link', creation_dblink as 'creation_dblink'));
SELECT owner , db_link , DECODE(lv, 1, 'owner', 2, 'drop_link', 3, 'creation_dblink') y , DECODE(lv , 1, '--as connect '||owner||'; --//you have to connect each schema' , 2, '--DROP '||DECODE(owner, 'PUBLIC', 'PUBLIC')||' DATABASE LINK '||db_link||';' , 3, REPLACE(REPLACE(dbms_metadata.get_ddl('DB_LINK', db_link, owner), CHR(10)), '"')||';' ) x FROM dba_db_links , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3) ;
마농님 근데요.
제가 원하는 결과가 아니라서요(상차려줘도 못먹나?)
원하는 결과가 아래와 같이 나타났으며 해서요
X
----
--as connect PUBLIC;
--DROP PUBLIC DATABASE LINK AAA;
CREATE PUBLIC DATABASE LINK AAA CONNECT TO CIMPROD IDENTIFIED BY VALUES ':1' USING 'ADB';
--as connect BBB;
--DROP PUBLIC DATABASE LINK BBBCC;
CREATE PUBLIC DATABASE LINK BBBCC CONNECT TO CIMPROD IDENTIFIED BY VALUES ':1' USING 'BBBCCDB';