다른 명령
전환 환경 최적화 설정
(필요시) TEMP 테이블스페이스의 크기 늘려줌
- 메모리에서 정렬이 모두 이루어지지 않는 경우 TEMP 테이블스페이스의 크기도 늘려줘야 함 - 임시 테이블스페이스를 별도의 큰 tempfile(datafile이 아닌)로 구성(시간 단축) - 오라클 사용자의 TEMPORARY TABLESPACE IMSI 로 변경
(필요시) PGA 영역의 크기를 늘려줌
- init(spfile)의 Parameter 조정 - HASH_AREA_SIZE의 1/2을 SORT_AREA_SIZE로 사용 가능
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL; ALTER SESSION SET SORT_AREA_SIZE = 2147483647; ALTER SESSION SET SORT_AREA_RETAINED_SIZE = 2147483647; ALTER SESSION SET HASH_AREA_SIZE = 2147483647;
UNUSABLE된 인덱스를 ACCESS하지 않도록 설정(9i 이상)
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
PARALLEL이 가능하도록, SORT영역을 크게, 한번에 읽는 블록의 갯수가 많도록 설정
ALTER SESSION ENABLE PARALLEL DDL; ALTER SESSION ENABLE PARALLEL DML; ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL; ALTER SESSION SET SORT_AREA_SIZE=512000000; ALTER SESSION SET SORT_AREA_RETAINED_SIZE=512000000; ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256; ALTER SESSION SET "_sort_multiblock_read_count" = 128; ALTER SESSION SET "_db_file_optimizer_read_count" = 128; ALTER SESSION SET "_db_file_exec_read_count" = 128; ALTER SESSION SET "_serial_direct_read" = TRUE;
작업 대상 추출
- 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 ('CYKADM') 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;
ZB TABLE 생성 (FROM ERWIN)
OBJECT 확인
SELECT OWNER, 'T'||SUBSTR(TABLE_NAME,2), COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_PRECISION,DATA_SCALE FROM DBA_TAB_COLUMNS WHERE OWNER = 'CYKADM' AND TABLE_NAME IN ('Z_MD_XXX') MINUS SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_PRECISION,DATA_SCALE FROM DBA_TAB_COLUMNS WHERE OWNER = 'CYKADM' AND TABLE_NAME IN ('Z_MD_XXX') ;
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_PRECISION,DATA_SCALE FROM DBA_TAB_COLUMNS WHERE OWNER = 'CYKADM' AND TABLE_NAME IN ('Z_MD_XXX') 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 = 'CYKADM' AND TABLE_NAME IN ('Z_MD_XXX') ;
데이타 COPY
SELECT 'INSERT /*+ APPEND */ INTO Z_'||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_XXX');
데이터 이관중인 테이블스페이스 사이즈 조회
-- 데이터 전환중인 테이블스페이스 사이즈 조회 SELECT A.OWNER , A.SEGMENT_NAME , A.PARTITION_NAME , A.SEGMENT_TYPE , A.SEGMENT_SUBTYPE , A.TABLESPACE_NAME , A.BYTES/1024/1024/1024 GB FROM DBA_SEGMENTS A WHERE OWNER = 'MIGBASEDATA' AND SEGMENT_TYPE='TEMPORARY' ;
인덱스 생성
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 = 'CYKADM' 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_XXX') 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 = 'CYKADM' 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_XXX') GROUP BY A.INDEX_OWNER,A.TABLE_NAME,A.INDEX_NAME ;
권한 부여 SCRIPT 수행
SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' FROM DBA_TAB_PRIVS WHERE OWNER = 'CYKADM' AND TABLE_NAME IN ( 'TB_MD_XXX' );
권한 점검
SELECT GRANTEE, OWNER,TABLE_NAME,GRANTOR,PRIVILEGE FROM DBA_TAB_PRIVS WHERE OWNER = 'CYKADM' AND TABLE_NAME IN ('TB_MD_XXX') MINUS SELECT GRANTEE, OWNER,'T'||SUBSTR(TABLE_NAME,2),GRANTOR,PRIVILEGE FROM DBA_TAB_PRIVS WHERE OWNER = 'CYKADM' AND TABLE_NAME IN ('Z_MD_GDS_SEL_CHNL_L_H');
RENAME 작업
- T -> XT, Z -> T 전환 (제약사항,테이블명,인덱스 변경 )
- CONSTRAINT 백업 PK -> XPK 로
- TABLE RENAME 백업 T -> X
- CONSTRAINT 전환 ZPK-> PK
- TABLE RENAME 전환 Z ->T
- INDEX RENAME I -> X로
- Z_INDEX RENAME Z -> IX
SELECT STATEMENT FROM ( SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' RENAME CONSTRAINT '||'PK_'||SUBSTR(TABLE_NAME,4)||' TO '||'XPK_'||SUBSTR(TABLE_NAME,4)||';'||CHR(10)|| 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' RENAME TO '||'X'||SUBSTR(TABLE_NAME,2)||';'||CHR(10)|| 'ALTER TABLE '||OWNER||'.'||'Z'||SUBSTR(TABLE_NAME,2)||' RENAME CONSTRAINT '||'ZPK_'||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 ('CYKADM') UNION ALL SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' RENAME TO '||'X'||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 ('CYKADM') ) A WHERE TABLE_NAME IN ('TB_MD_XXX');
- PK 인덱스 생성 - UNIQUE 인덱스 생성 하여 PK 생성 작업
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 = 'CYKADM' 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_XXX' ) GROUP BY A.INDEX_OWNER,A.TABLE_NAME,A.INDEX_NAME ORDER BY A.INDEX_OWNER,A.TABLE_NAME,A.INDEX_NAME;
- PK 인덱스 생성
-- 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 = 'CYKADM' 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_XXX') GROUP BY A.INDEX_OWNER,A.TABLE_NAME,A.INDEX_NAME ORDER BY A.INDEX_OWNER,A.TABLE_NAME,A.INDEX_NAME;