행위

데이터이행 DDL작업

DB CAFE

Dbcafe (토론 | 기여)님의 2020년 1월 8일 (수) 13:29 판 (→‎전환 환경 설정)
thumb_up 추천메뉴 바로가기

1 전환 환경 최적화 설정[편집]

1.1 (필요시) TEMP 테이블스페이스의 크기 늘려줌[편집]

  - 메모리에서 정렬이 모두 이루어지지 않는 경우 TEMP 테이블스페이스의 크기도 늘려줘야 함
  - 임시 테이블스페이스를 별도의 큰 tempfile(datafile이 아닌)로 구성(시간 단축)
  - 오라클 사용자의 TEMPORARY TABLESPACE IMSI 로 변경

1.2 (필요시) 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;

1.3 UNUSABLE된 인덱스를 ACCESS하지 않도록 설정(9i 이상)[편집]

ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

1.4 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;

2 작업 대상 추출[편집]

- 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;

3 ZB TABLE 생성 (FROM ERWIN)[편집]

4 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')
 ;


5 데이타 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');

6 인덱스 생성[편집]

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
;

7 권한 부여 SCRIPT 수행[편집]

SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';'
FROM DBA_TAB_PRIVS
WHERE OWNER = 'CYKADM'
AND TABLE_NAME IN (
'TB_MD_XXX'
);

8 권한 점검[편집]

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');

9 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;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=데이터이행_DDL작업&oldid=2963"