테이블 DDL 추출 스크립트

테이블 DDL 추출 스크립트

SET SERVEROUTPUT ON;

DECLARE
	CURSOR c_tables IS
    /***********DDL 추출 대상 테이블 수정 ***********/
    	SELECT TABLE_NAME
             , OWNER
          FROM DBA_TABLES
         WHERE OWNER = 'IGWOWN'
           AND TABLE_NAME LIKE 'BC_AFBZ_OR_C';
   /************************************************/
   
   v_ddl CLOB;
   
 BEGIN
 	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PARTITIONING',true);
    
    FOR r_table IN c_tables LOOP
    	v_ddl := v_ddl || '/******** [ START DDL for ' || r_table.table_name ||' ] *********/';
        v_ddl := v_ddl || CHR(10) || '-- 1.TABLE(PK INDEX 및 CONSTRAINT 포함)' || dbms_metadata.get_ddl('TABLE',r_table.table_name, r_table.owner);
        v_ddl := v_ddl || CHR(10) || CHR(10) || '-- 2.INDEX(PK INDEX 외)' ;
        
        FOR r_index IN (SELECT INDEX_NAME
                          FROM DBA_INDEXES
                         WHERE TABLE_NAME = r.table.table_name
                           AND TABLE_OWNER = r_table.owner 
                           AND INDEX_NAME NOT LIKE 'PK%' LOOP
        	BEGIN v_ddl := || dbms_metadata.get_ddl('INDEX',r_index.index_name, r_table.owner);
            	EXCEPTION
                	WHEN OTHERS THEN
                    	NULL;
            END;
        END LOOP; 
        
        v_ddl := v_ddl || CHR(10) || CHR(10) || '-- 3.COMMENT' || dbms_metadata.get_dependent_ddl('COMMENT','r_table.table_name, r_table.owner);
        v_ddl := v_ddl || CHR(10) || CHR(10) || '-- 4.GRANT' || dbms_metadata.get_dependent_ddl('OBJECT_GRANT',r_table.table_name,r_table.owner);
        v_ddl := CHR(10) || '/*************[END DDL for ' || r_table.table_name || ' ] ***********/' || CHR(10) || CHR(10);
        
        dbms_output.put_line(v_ddl);
        
    END LOOP;
 END;