데이터이행 DDL작업

0. 작업 대상 추출 - Non Partitioned Table 먼저 진행

select b.owner,b.table_name,b.column_name from (

   select owner, table_name, max(column_id) column_id
   from dba_tab_columns
   where owner in ('OOADM')
   group by owner, table_name) a, dba_tab_columns b

where a.owner = b.owner and a.table_name = b.table_name and a.column_id = b.column_id and b.column_name != 'MOD_DT' and not exists (select 1 from dba_part_tables c where a.table_name = c.table_name) order by b.owner, b.table_name;

1. ZB Table 생성 (From ERwin)

2. Object 확인. -- OYDBA 계정에서 작업

select owner, 'T'||substr(table_name,2), column_name, data_type, data_length, nullable, data_precision,data_scale from dba_tab_columns where owner = 'OOADM' and table_name in ( 'ZB_MD_GDS_SEL_CHNL_L_H' ) minus select owner, table_name, column_name, data_type, data_length, nullable, data_precision,data_scale from dba_tab_columns where owner = 'OOADM' and table_name in ( 'TB_MD_GDS_SEL_CHNL_L_H' );

select owner, table_name, column_name, data_type, data_length, nullable, data_precision,data_scale from dba_tab_columns where owner = 'OOADM' and table_name in ( 'TB_MD_GDS_SEL_CHNL_L_H' ) minus select owner, 'T'||substr(table_name,2), column_name, data_type, data_length, nullable, data_precision,data_scale from dba_tab_columns where owner = 'OOADM' and table_name in ( 'ZB_MD_GDS_SEL_CHNL_L_H' );

3. 데이타 Copy -- OOADM 계정에서 작업

SELECT 'INSERT /*+ APPEND */ INTO ZB_'||MAX(SUBSTR(a.table_name,4))||' NOLOGGING'||CHR(10)||

      '( '||LISTAGG(a.column_name, CHR(10)||',') WITHIN GROUP (ORDER BY a.column_id) ||CHR(10)||
      ') '||CHR(10)||
      'SELECT '||CHR(10)||'B.'||LISTAGG(a.column_name, CHR(10)||',B.') WITHIN GROUP (ORDER BY a.column_id)||CHR(10)||
      'FROM '||MAX(a.table_name)||' B;'||CHR(10)||
      'COMMIT;'

FROM all_tab_columns a, all_col_comments b WHERE a.table_name = b.table_name

 AND a.column_name = b.column_name
 AND a.table_name in (

'TB_MD_GDS_SEL_CHNL_L_H' -- TB 테이블 );

3. 인덱스 생성

select 'CREATE '||decode(max(b.UNIQUENESS),'UNIQUE','UNIQUE')||' INDEX '||a.index_owner||'.'||'Z'||substr(a.index_name,2)||CHR(10)||

      ' ON '||index_owner||'.'||'Z'||substr(a.table_name,2)||' ('||LISTAGG(a.column_name, ',') WITHIN GROUP (ORDER BY a.column_position)||')'||CHR(10)||
      (select locality||CHR(10) from dba_part_indexes c where a.index_name = c.index_name) ||
      'TABLESPACE '||max(b.tablespace_name)||(select def_tablespace_name from dba_part_indexes c where a.index_name = c.index_name)||CHR(10)||
      'NOLOGGING;'||CHR(10)||
      'ALTER INDEX '||a.index_owner||'.'||'Z'||substr(a.index_name,2)||' LOGGING;'

from dba_ind_columns a, dba_indexes b where index_owner = 'OOADM' and a.index_owner = b.owner and a.index_name = b.index_name and a.index_name not like '%_OLD' and b.table_name in ( 'TB_MD_GDS_SEL_CHNL_L_H' ) group by a.index_owner,a.table_name,a.index_name UNION ALL -- PK Constraint select 'ALTER TABLE '||index_owner||'.'||'Z'||substr(a.table_name,2)||' ADD CONSTRAINT '||'Z'||substr(a.index_name,2)||' PRIMARY KEY ('||LISTAGG(a.column_name, ',') WITHIN GROUP (ORDER BY a.column_position)||');'||CHR(10) from dba_ind_columns a, dba_indexes b where index_owner = 'OOADM' and a.index_owner = b.owner and a.index_name = b.index_name and (a.index_name like 'PK%' and a.index_name not like '%_OLD') and b.table_name in ( 'TB_MD_GDS_SEL_CHNL_L_H' ) group by a.index_owner,a.table_name,a.index_name

4. 권한 부여 Script 수행

select 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||grantee||';' from dba_tab_privs where owner = 'OOADM' and table_name in ( 'TB_MD_GDS_SEL_CHNL_L_H' );

5. 권한 점검

select grantee, owner,table_name,grantor,privilege from dba_tab_privs where owner = 'OOADM' and table_name in ( 'TB_MD_GDS_SEL_CHNL_L_H' ) MINUS select grantee, owner,'T'||SUBSTR(table_name,2),grantor,privilege from dba_tab_privs where owner = 'OOADM' and table_name in ( 'ZB_MD_GDS_SEL_CHNL_L_H' );

6. Rename 작업

TB -> YB, ZB -> TB

select STATEMENT from ( select 'ALTER TABLE '||owner||'.'||table_name||' RENAME CONSTRAINT '||'PK_'||substr(table_name,4)||' TO '||'YK_'||substr(table_name,4)||';'||CHR(10)||

      'ALTER TABLE '||owner||'.'||table_name||' RENAME TO '||'Y'||substr(table_name,2)||';'||CHR(10)||
      'ALTER TABLE '||owner||'.'||'Z'||substr(table_name,2)||' RENAME CONSTRAINT '||'ZK_'||substr(table_name,4)||' TO '||'PK_'||substr(table_name,4)||';'||CHR(10)||
      'ALTER TABLE '||owner||'.'||'Z'||substr(table_name,2)||' RENAME TO '||table_name||';' AS STATEMENT
      ,table_name

from dba_tables where owner in ('OOADM') UNION ALL select 'ALTER INDEX '||owner||'.'||index_name||' RENAME TO '||'Y'||substr(index_name,2)||';'||CHR(10)||

      'ALTER INDEX '||owner||'.'||'Z'||substr(index_name,2)||' RENAME TO '||index_name||';' AS STATEMENT
      ,table_name

from dba_indexes where owner in ('OOADM') ) A where table_name in ( 'TB_MD_GDS_SEL_CHNL_L_H' );

select 'CREATE '||decode(max(b.UNIQUENESS),'UNIQUE','UNIQUE')||' INDEX '||a.index_owner||'.'||'Z'||substr(a.index_name,2)||CHR(10)||

      ' ON '||index_owner||'.'||'Z'||substr(a.table_name,2)||' ('||LISTAGG(a.column_name, ',') WITHIN GROUP (ORDER BY a.column_position)||')'||CHR(10)||
      (select locality||CHR(10) from dba_part_indexes c where a.index_name = c.index_name) ||
      'TABLESPACE '||max(b.tablespace_name)||(select def_tablespace_name from dba_part_indexes c where a.index_name = c.index_name)||CHR(10)||
      'NOLOGGING;'||CHR(10)||
      'ALTER INDEX '||a.index_owner||'.'||'Z'||substr(a.index_name,2)||' LOGGING;'

-- PK Constraints:

select 'ALTER TABLE '||index_owner||'.'||'Z'||substr(a.table_name,2)||' ADD CONSTRAINT '||'Z'||substr(a.index_name,2)||' PRIMARY KEY ('||LISTAGG(a.column_name, ',') WITHIN GROUP (ORDER BY a.column_position)||');'||CHR(10) from dba_ind_columns a, dba_indexes b where index_owner = 'OOADM' and a.index_owner = b.owner and a.index_name = b.index_name and (a.index_name like 'PK%' and a.index_name not like '%_OLD') and b.table_name in ( 'TB_LO_CNTR_RNP_GDS_DD_A' ) group by a.index_owner,a.table_name,a.index_name order by a.index_owner,a.table_name,a.index_name;

데이터이행 DDL작업

DB CAFE