"데이터이행 DDL작업"의 두 판 사이의 차이
DB CAFE
(새 문서: 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_t...) |
|||
1번째 줄: | 1번째 줄: | ||
+ | == 0. 작업 대상 추출 == | ||
+ | - NON PARTITIONED TABLE 먼저 진행 | ||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | </source> | ||
− | + | == 1. ZB TABLE 생성 (FROM ERWIN) == | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | == 2. OBJECT 확인 == | |
+ | <source lang=sql> | ||
+ | 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') | ||
+ | ; | ||
+ | </source> | ||
+ | <source lang=sql> | ||
+ | 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') | ||
+ | ; | ||
+ | </source> | ||
− | |||
− | + | == 3. 데이타 COPY == | |
− | + | <source lang=sql> | |
− | + | 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)|| | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | 3. 데이타 | ||
− | |||
− | SELECT 'INSERT /*+ APPEND */ INTO | ||
− | '( '||LISTAGG( | ||
') '||CHR(10)|| | ') '||CHR(10)|| | ||
− | 'SELECT '||CHR(10)||'B.'||LISTAGG( | + | 'SELECT '||CHR(10)||'B.'||LISTAGG(A.COLUMN_NAME, CHR(10)||',B.') WITHIN GROUP (ORDER BY A.COLUMN_ID)||CHR(10)|| |
− | 'FROM '||MAX( | + | ' FROM '||MAX(A.TABLE_NAME)||' B;'||CHR(10)|| |
'COMMIT;' | 'COMMIT;' | ||
− | FROM | + | FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B |
− | WHERE | + | WHERE A.TABLE_NAME = B.TABLE_NAME |
− | + | AND A.COLUMN_NAME = B.COLUMN_NAME | |
− | + | AND A.TABLE_NAME IN ('TB_MD_XXX'); | |
− | ' | + | </source> |
− | ); | ||
+ | == 3. 인덱스 생성 == | ||
+ | <source lang=sql> | ||
+ | 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 | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | ' | ||
− | ) | ||
− | |||
; | ; | ||
− | + | </source> | |
− | 4. 권한 부여 | + | == 4. 권한 부여 SCRIPT 수행 == |
− | + | <source lang=sql> | |
− | + | SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' | |
− | + | FROM DBA_TAB_PRIVS | |
− | + | WHERE OWNER = 'CYKADM' | |
− | + | AND TABLE_NAME IN ( | |
− | ' | + | 'TB_MD_XXX' |
); | ); | ||
+ | </source> | ||
+ | == 5. 권한 점검 == | ||
+ | <source lang=sql> | ||
+ | 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'); | ||
+ | </source> | ||
+ | == 6. 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 | ||
+ | <source lang=sql> | ||
+ | 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'); | ||
+ | </source> | ||
+ | ---------------- | ||
+ | * PK 인덱스 생성 - UNIQUE 인덱스 생성 하여 PK 생성 작업 | ||
− | + | <source lang=sql> | |
− | + | 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)|| | |
− | ' ON '|| | + | (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)|| |
− | 'TABLESPACE '|| | ||
'NOLOGGING;'||CHR(10)|| | 'NOLOGGING;'||CHR(10)|| | ||
− | 'ALTER INDEX '|| | + | '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; | |
+ | </source> | ||
+ | * PK 인덱스 생성 | ||
− | -- PK | + | -- PK CONSTRAINTS: |
− | + | <source lang=sql> | |
− | + | 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; | |
− | + | </source> |
2018년 11월 2일 (금) 17:28 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 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 ('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;
2 1. ZB TABLE 생성 (FROM ERWIN)[편집]
3 2. 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')
;
4 3. 데이타 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');
5 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 = '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
;
6 4. 권한 부여 SCRIPT 수행[편집]
SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';'
FROM DBA_TAB_PRIVS
WHERE OWNER = 'CYKADM'
AND TABLE_NAME IN (
'TB_MD_XXX'
);
7 5. 권한 점검[편집]
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');
8 6. 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;