Metadata DDL 추출

-- DB_LINK

SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link, owner)
FROM   dba_db_links
order by owner,username;

 

-- JOB

SET SERVEROUTPUT ON
SET LINESIZE 4000

DECLARE
CURSOR C1 IS
    SELECT JOB FROM DBA_JOBS WHERE UPPER(WHAT) LIKE '%DBMS_REFRESH%' ORDER BY JOB;
    CNT INTEGER:=1;
    JOB_NO BINARY_INTEGER;
    CALLSTR VARCHAR2(1000);
BEGIN
SELECT COUNT(1) INTO CNT FROM DBA_JOBS WHERE UPPER(WHAT) LIKE '%DBMS_REFRESH%';
OPEN C1;
FOR I IN 1..CNT LOOP
FETCH C1 INTO JOB_NO;
DBMS_OUTPUT.PUT_LINE('JOB NO <<< '||JOB_NO||' >>>');
DBMS_OUTPUT.PUT_LINE('===========================');
DBMS_OUTPUT.PUT_LINE('CREATE DDL ====>');
DBMS_OUTPUT.PUT_LINE('===========================');
DBMS_JOB.USER_EXPORT(JOB_NO,CALLSTR);
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('  '||CALLSTR);
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('===========================');
DBMS_OUTPUT.PUT_LINE('REMOVE DDL ====>');
DBMS_OUTPUT.PUT_LINE('===========================');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_JOB.REMOVE('||JOB_NO||');');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('===========================');

END LOOP;
CLOSE C1;
DBMS_OUTPUT.PUT_LINE('COMMIT;');
END;

 

-- Mview

SELECT a.OWNER,MVIEW_NAME,MASTER_LINK,REFRESH_METHOD,LAST_REFRESH_DATE,COMPILE_STATE,dbms_metadata.get_ddl(replace(object_type,' ','_'), object_name,a.owner) as ddl
 FROM   dba_objects a,
        dba_mviews b
 where a.owner = b.owner
 and  a.object_name = b.mview_name
 and object_type in ('MATERIALIZED VIEW')
order by a.OWNER,MVIEW_NAME,MASTER_LINK;

 

-- Trigger

SELECT B.OWNER, B.MVIEW_NAME, A.OWNER TRIGGER_OWNER, A.TRIGGER_NAME, TRIGGER_TYPE,
        TRIGGERING_EVENT,BASE_OBJECT_TYPE,
        A.STATUS,DBMS_METADATA.GET_DDL(REPLACE('TRIGGER',' ','_'), C.OBJECT_NAME,A.OWNER) AS DDL
 FROM   DBA_TRIGGERS A,
        DBA_MVIEWS   B,
        DBA_OBJECTS  C
 WHERE  A.TABLE_OWNER = B.OWNER
 AND    A.TABLE_NAME = B.MVIEW_NAME
 AND    A.OWNER = C.OWNER
 AND    A.TRIGGER_NAME = C.OBJECT_NAME
 AND    C.OBJECT_TYPE='TRIGGER'
ORDER BY B.OWNER, B.MVIEW_NAME, A.OWNER , A.TRIGGER_NAME;

 

-- User

-- -----------------------------------------------------------------------------------
-- File Name    : user_ddl.sql
-- -----------------------------------------------------------------------------------

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_username VARCHAR2(30);

exec:v_username := upper('&USERNAME');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on