데이터이행 DDL작업
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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;'
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 order by a.index_owner,a.table_name,a.index_name;
-- 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;