테이블과 인덱스 생성 스크립트 추출 방법

테이블과 인덱스 생성 스크립트 추출 방법

1.번 수행 후, 수행결과 나온 것을 수행하면 됨

 

1. mk_WCDMA_get_part_table.sql

spool idba_get_WCDMA_part_table.sql

SET PAGESIZE 0
SET LINESIZE 132
SET FEEDBACK OFF
SET ECHO OFF

select '@/home/oracle/DBA/11.TABLE/IDBA/OUTPUT_SCRIPTS_20120618/mk_getddl_part_table.sql '||table_name ||' '|| TABLE_owner||' '||tablespace_name
from all_tab_partitions
        WHERE   TABLE_OWNER= 'IDBA'
        AND     substr(partition_name,1,11) = 'PT_20120618'
        AND     TABLE_NAME NOT LIKE '%WRK_%'
        AND     table_name like '%IPMS%'
        AND     table_name not like '%RIPMS%'    
        AND     table_name not like '%CR_RT%'    
        AND     table_name not like '%CP%'
        AND     table_name not like 'LD_%'
        OR     table_name in ('DROP_UDBS_META_CDR','DROP_UDBS_META_UDR','LTE_UDBS_UDR_04','LTE_UDBS_CDR_04')
        group by TABLE_NAME,TABLE_owner,tablespace_name
        order by TABLE_NAME
        ;

spool off

 

2. mk_getddl_part_table.sql

spool cr_&&1..sql
SET SERVEROUTPUT ON
SET LINESIZE 120
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF

DECLARE
    CURSOR cu_tab IS

        SELECT
                table_owner,
                table_name,
                tablespace_name,
                pt_name,
                h_value,
                logging,
                compression,
                compress_for,
                part_key,
                part_type
        FROM
                (SELECT a.table_owner, a.table_name, a.tablespace_name, a.pt_name,
                        a.logging, a.compression, a.compress_for,
                        b.high_value h_value, c.partitioning_type part_type, d.column_name part_key
                FROM
                        (SELECT         atp.table_owner,
                                        atp.table_name,
                                        max(atp.partition_name) pt_name,
                                        atp.tablespace_name,
                                        atp.logging,
                                        atp.compression,
                                        atp.compress_for
                        FROM    all_tab_partitions atp
                        WHERE  atp.table_name  = Decode(Upper('&&1'), 'ALL',atp.table_name, Upper('&&1'))
                        AND    atp.table_owner = Upper('&&2')
                        GROUP BY table_owner,table_name,tablespace_name,logging,compression,compress_for) a,
                        all_tab_partitions b, all_part_tables c, all_part_key_columns d
                WHERE   a.pt_name = b.partition_name
                AND     a.table_name = c.table_name
                AND     a.table_name = d.name
                ORDER BY a.pt_name DESC)
        WHERE   rownum =1;
 
    CURSOR cu_col (in_table  IN  VARCHAR2) IS
        SELECT  a.column_name,
                a.data_type,
                Decode(a.data_scale,NULL,'',0,'',',' || To_Char(a.data_scale)) data_scale,
                To_Char(a.data_length) data_length,
                To_Char(a.data_precision) data_precision,
                Decode(a.nullable,'N','NOT NULL') nullable
        FROM    all_tab_columns a
        WHERE   a.table_name  = in_table
        AND     a.owner       = Upper('&&2')
        ORDER BY a.column_id;

    CURSOR cu_ind IS
        SELECT
                a.index_name,
                a.table_name,
                a.def_logging logging,
                a.locality local,
                b.PREFIX_LENGTH IND_COMPRESS,
                a.def_tablespace_name tablespace_name
        FROM    all_part_indexes a, ALL_INDEXES b
        WHERE   a.table_name  = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND     a.owner       = Upper('&&2')
        AND     a.index_name=b.index_name
        ORDER BY a.index_name;

    CURSOR cu_ind_col (in_index  IN  VARCHAR2) IS
        SELECT  a.column_name col_name
        FROM    all_ind_columns a
        WHERE   a.index_name  = in_index
        AND     a.index_owner       = Upper('&&2')
        AND     a.table_name  = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'));

    CURSOR cu_tab_com IS
        SELECT  a.table_name,
                a.comments
        FROM    all_tab_comments a
        WHERE   a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND     a.owner      = Upper('&&2')
        AND     a.comments  IS NOT NULL;

    CURSOR cu_col_com IS
        SELECT  a.table_name,
                a.column_name,
                a.comments
        FROM    all_col_comments a
        WHERE   a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND     a.owner      = Upper('&&2')
        AND     a.comments  IS NOT NULL;

BEGIN

    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    FOR cur_tab IN cu_tab LOOP
        DBMS_Output.Put_Line('PROMPT Creating Table '|| cur_tab.table_owner||'.'|| cur_tab.table_name);
        DBMS_Output.Put_Line(chr(9));
        DBMS_Output.Put_Line('--DROP TABLE ' || cur_tab.table_owner||'.'|| cur_tab.table_name||';');
        DBMS_Output.Put_Line('CREATE TABLE ' || cur_tab.table_owner||'.'|| cur_tab.table_name);
        DBMS_Output.Put_Line('(');
        FOR cur_col IN cu_col (in_table => cur_tab.table_name) LOOP
            IF cu_col%ROWCOUNT != 1 THEN
                DBMS_Output.Put_Line(',');
            END IF;
            IF cur_col.data_type = 'DATE' THEN
                DBMS_Output.Put(chr(9)||RPad(cur_col.column_name,30,' ') || ' ' || RPad(cur_col.data_type,20,' ') || ' ' || cur_col.nullable);    
            ELSIF cur_col.data_type = 'NUMBER' THEN
                IF cur_col.data_precision is null THEN
                        DBMS_Output.Put(chr(9)||RPad(cur_col.column_name,30,' ') || ' ' || RPad(cur_col.data_type,20,' ') || ' ' || cur_col.nullable);   
                ELSE
                        DBMS_Output.Put(chr(9)||RPad(cur_col.column_name,30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_precision || cur_col.data_scale || ')',20,' ') || ' ' || cur_col.nullable);    
                END IF;
            ELSE
                DBMS_Output.Put(chr(9)||RPad(cur_col.column_name,30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_length || ')',20,' ') || ' ' ||  cur_col.nullable);    
            END IF;
        END LOOP;
        DBMS_Output.Put_Line(' ');
        DBMS_Output.Put_Line(')');
        DBMS_Output.Put_Line('TABLESPACE ' || cur_tab.tablespace_name);
            IF cur_tab.logging = 'NO' THEN
                DBMS_Output.Put_Line('NOLOGGING ');
            END IF;
            IF cur_tab.compression = 'ENABLED' THEN
                DBMS_Output.Put_Line('COMPRESS FOR ' || cur_tab.compress_for);
            END IF;

        DBMS_Output.Put_Line(chr(9)||'PARTITION BY ' || cur_tab.part_type||'('||cur_tab.part_key||')');
        DBMS_Output.Put_Line(chr(9)||'(');
        DBMS_Output.Put_Line(chr(9)||'  PARTITION '|| cur_tab.pt_name || ' VALUES LESS THAN (' || cur_tab.h_value || ')');
        DBMS_Output.Put_Line(chr(9)||')');
        DBMS_Output.Put_Line(';');
    END LOOP;
    DBMS_Output.Put_Line(chr(9));
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Create/Recreate primary, unique and foreign key constraints');
    DBMS_Output.Put_Line(' ' ||chr(9));
    FOR cur_ind IN cu_ind LOOP
        DBMS_Output.Put_Line('CREATE INDEX '||cur_ind.index_name||' ON '||cur_ind.table_name||'(');
        FOR cur_ind_col IN cu_ind_col (in_index => cur_ind.index_name) LOOP
            IF cu_ind_col%ROWCOUNT != 1 THEN
                DBMS_Output.Put_Line(',');
            END IF;
                DBMS_Output.Put(chr(9)||cur_ind_col.col_name);    
        END LOOP;
        DBMS_Output.Put_Line(  ') ' ||chr(9));
        DBMS_Output.Put_Line(case when cur_ind.local is null then cur_ind.logging  || 'LOGGING '
                                  when cur_ind.local is not null then cur_ind.logging  || 'LOGGING '||chr(13)||chr(10)||cur_ind.local
                             end
                            );
        DBMS_Output.Put_Line(case when cur_ind.tablespace_name is null and cur_ind.IND_COMPRESS is null
                                 then 'PARALLEL 32;'
                            when cur_ind.tablespace_name is not null and cur_ind.IND_COMPRESS is null
                                 then 'TABLESPACE '||cur_ind.tablespace_name||chr(13)||chr(10)||'PARALLEL 32;'
                            when cur_ind.tablespace_name is null and cur_ind.IND_COMPRESS is not null
                                 then 'COMPRESS '||cur_ind.IND_COMPRESS||chr(13)||chr(10)||'PARALLEL 32;'
                            when cur_ind.tablespace_name is not null and cur_ind.IND_COMPRESS is not null
                                 then 'TABLESPACE '||cur_ind.tablespace_name||chr(13)||chr(10)||'COMPRESS '||cur_ind.IND_COMPRESS||chr(13)||chr(10)||'PARALLEL 32;'
                        end
                            );
        DBMS_Output.Put_Line(  ' ' ||chr(9));
    END LOOP;
    DBMS_Output.Put_Line(' ' ||chr(9));
    DBMS_Output.Put_Line(' ' ||chr(9));
    FOR cur_ind IN cu_ind LOOP
        DBMS_Output.Put_Line('ALTER INDEX '||cur_ind.index_name||' NOPARALLEL;');
    END LOOP;
    DBMS_Output.Put_Line(' ' ||chr(9));
    DBMS_Output.Put_Line(' ' ||chr(9));
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Creating Table Comment');
    DBMS_Output.Put_Line(' ' ||chr(9));
    FOR cur_tab_com IN cu_tab_com LOOP
        DBMS_Output.Put_Line('COMMENT ON TABLE ' || Lower(cur_tab_com.table_name) || ' IS ''' || cur_tab_com.comments || ''';');
    END LOOP;
    DBMS_Output.Put_Line(' ' ||chr(9));
    DBMS_Output.Put_Line(' ' ||chr(9));
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Creating Column Comments');
    DBMS_Output.Put_Line(' ' ||chr(9));
    FOR cur_col_com IN cu_col_com LOOP
        DBMS_Output.Put_Line('COMMENT ON COLUMN ' || Lower(cur_col_com.table_name || '.' || cur_col_com.column_name) || ' IS ''' || cur_col_com.comments || ''';');
    END LOOP;
    DBMS_Output.Put_Line(' ' ||chr(9));
END;
/

select '!/home/oracle/DBA/32.PARTITION/22.run_MONTH_DAY_add_PT_HOUR_YYYYMMDDHH24MISS.sh '||'&&1'||' 201207 '||'&&3'||' '
from dual;

spool off