How to Reorganize a Table

1.CTAS

사용방법

1) 테이블 구조(METADATA)를 백업(EXP)

아래의 명령어(EXP or EXPDP)로 백업 혹은 스크립트 형태로 백업 받아놓습니다.

 exp username/password file=test.dmp tables=test rows=n
 (Or) 
 expdp username/password directory=<dirname> dumpfile=test.dmp tables=test

 

2) 원본테이블로 부터 Target Table로 Copy

 create table new_test as select * from test;

3)원본 테이블 및 index 와 constraint 삭제

(주의 : 이 명령어는 관련 index와 constraint 를 모두 삭제합니다)

drop table test cascade constraints;

4)Target Table Rename

 alter table new_test rename to test;

5)Index 및 constraint 재생성

(주의 : imp 시 table이 이미 있어서 error가 발생할 수 있습니다. 무시해도 됩니다)

 imp username/password file=test.dmp ignore=y

 

장점

1) 사용이 쉽습니다.

2) 사용 중에도 원본 테이블에 대한 DML이 가능합니다.(CTAS가 시작된 이후의 DML은 copy 되지 않음)

 

단점

1) 느린 작업 속도

2) 추가 Tablespace 내의 작업 공간 필요 

3) CTAS가 시작되고 나서 DML은 Target Table의 데이터가 기록되지 않습니다.

4) Target Table에 Index과 Constraint를 추가로 생성해야 합니다.

 

2.EXPDP/IMPDP (EXP/IMP)

사용방법

1)EXP or EXPDP 하기

exp username/password file=test.dmp tables=test 
(Or)
expdp username/password directory=<dirname> dumpfile=test.dmp tables=test

2)원본 테이블과 Index 및 constraint Drop

(주의 : 이 명령어는 관련 index와 constraint 를 모두 삭제합니다)

drop table test cascade constraints;

 

3)테이블에 IMP 하기

imp username/password file=test.dmp

 

장점

1)다른 reorg 방법과 비교했을 때 빠릅니다

2)IMP 전에 추가 Tablespace 내의 공간이 필요하지 않습니다. 

3)전체 테이블스페이스를 reorg할때 추천합니다.

4)재구성할 Object가 많을때 사용을 추천합니다.

단점

1)테이블 가용성에 영향을 받습니다. 계획 후에 downtime이 필요합니다.

2) EXP 후에 추가된 DML 데이터는 IMP되지 않습니다. (IMP 직전에 다시 EXP하면 유실 데이터를 최소화 할 수 있습니다)

 

3.ALTER TABLE MOVE TABLESPACE

사용방법

alter table TEST.TEST move tablespace TS_TEST;

alter index TEST.PK_TEST  rebuild tablespace TS_TEST;

장점

1)Shrink와 비교했을때 특히 빠른 작업속도를 보여줍니다

2) 명령어가 비교적 간단합니다.

 

단점

1)해당 작업 중에는 DML작업이 가능하지 않습니다.

2) 추가 Tablespace 내의 작업 공간 필요 

3) INDEX rebuild를 manually 하게 수행해야 합니다.

 

+) ONLINE 옵션

아래의 테이블에 사용가능합니다.

-> Table에 대해서 Reorg 시 ONLINE 옵션을 사용하여 테이블의 primary key를 재구성하는 도중 DML 작업을 할 수 있습니다.

-> Partition 및 Sub-Partition Table의 Reorg시에 ONLINE 옵션 사용가능합니다.

제약사항)

1.작업 중 Parallel DML이나 Direct path DML은 지원되지 않습니다.

2.도메인 인덱스가 있는 테이블은 지원되지 않습니다.

3. IOT 테이블은 지원되지 않습니다.

 

4.SHRINK

사용방법

ASSM 세그먼트에서만 사용할 수 있습니다. 하지만 online으로 DML 동작 중에도 Shrink 사용할 수 있습니다.

 Conventional DML는 사용가능 합니다. parallel DML은 사용불가합니다.

Shrink 단계에서는 개체의 가용성에 큰 영향을 주지 않습니다. 그러나 세그먼트가 축소되는 특정 단계(HWM이 조정되는 경우) 중에는 세그먼트를 배타적 모드로 잠가야 합니다. 이 단계는 매우 짧으며 해당 기간 동안의 가용성이 미치는 영향은 낮습니다.

1.테이블의 row movement 활성화 SQL> ALTER TABLE <table name> ENABLE ROW MOVEMENT; 2. 테이블 Shrink 하지만 HWM은 Shrink 하지 않기 SQL> ALTER TABLE <table name> SHRINK SPACE COMPACT; 3. 테이블 Shrink 와 HWM Shrink SQL> ALTER TABLE <table name> SHRINK SPACE; 4. 테이블 Shrink 와 모든 연관된 index SQL> ALTER TABLE <table name> SHRINK SPACE CASCADE; 5.MView에 있는 테이블 Shrink SQL> ALTER TABLE <table name> SHRINK SPACE; 6.Index만 Shrink SQL> ALTER INDEX <index name> SHRINK SPACE;

장점

1) 해당 작업중에 DML이 가능합니다.

2) 사용이 비교적 간단합니다

3) insert/delete가 쌍으로 이용되어 압축되므로 추가 tablespace 공간이 필요하지 않습니다.

4)CASCADE 옵션을 사용하여 INDEX를 자동으로 rebuild 해줍니다.

 

단점

1)Redo가 많이 발생됩니다.

2)큰 테이블의 Shrink를 수행할 때 수행시간이 오래걸립니다.

3) Reorg시 같은 Tablespace안에서만 가능합니다. 다른 Tablespace로의 MOVE는 불가능합니다.

 

5.ONLINE REDEFINITION OF TABLE

사용방법

가용성에 영챵을 미치지 않고 REORG 작업을 하는 방법입니다.

추가적인 제약사항이 많으므로 확인 후 사용하셔야 합니다.

 

장점

1) 테이블 가용성에 영향을 미치지 않습니다.

2) Index 및 constraint가 자동적으로 copy 됩니다.

3) LOBS와 큰 테이블작업에 추천합니다.

 

단점

1) 다른 reorg 옵션에 비해 작성해야 할 script가 많습니다.

2) dbms_redefinition 기본적으로 serial mode 로 인덱스를 생성합니다. 인덱스에 parallel mode 를 사용하려면 수동으로 생성 후 나중에 등록해주어야 합니다.

3) 가용성에 영향을 미치지 않아서 DML 이 가능하더라도 높은 트랜잭션 속도가 프로세스 동기화에 영향을 미칠 수 있으므로 사용량이 적은 시간에 수행하는 것이 좋습니다.   

https://bae9086.tistory.com/81

TTS 후 테이블이 이상이 있거나, 특이사항이 있을 때 새로 테이블을 생성하여(안의 데이터는 같음) 오브젝트 및 구성을 새로 만들어주는 편리한 프로시져입니다.   1. 작업 전 검증 exec dbms_redefinition.can_redef_table('<스키마명>','<테이블명>'); 2. 새로운 테이블 생성 CREATE TABLE INT_<테이블명> ... 3. Redefinition 시작 BEGIN dbms_redefinition.start_redef_table(      '<스키마명>',      '<테이블명>',      'INT_<테이블명>',      '<원본컬럼1> <타겟컬럼1>,   <원본컬럼2> <타겟컬럼2>,   ...   <원본컬럼n> <타겟컬럼n>',      dbms_redefinition.cons_use_pk); END; / 4. 종속 객체 복사 SET SERVEROUTPUT ON DECLARE   l_num_errors PLS_INTEGER; BEGIN   dbms_redefinition.copy_table_dependents(     uname             => '<스키마명>',     orig_table        => '<테이블명>',     int_table         => 'INT_<테이블명>',     copy_indexes      => dbms_redefinition.cons_orig_params, -- Non-Default     copy_triggers     => true,  -- Default     copy_constraints  => true,  -- Default     copy_privileges   => true,  -- Default     ignore_errors     => true, -- Default     num_errors        => l_num_errors);   dbms_output.put_line('l_num_errors=' || l_num_errors); END; / 5. 데이터 동기화 exec dbms_redefinition.sync_interim_table('<스키마명>','<테이블명>','INT_<테이블명>'); 6. Redefinition 완료 exec dbms_redefinition.finish_redef_table('<스키마명>','<테이블명>','INT_<테이블명>'); 7. 원복 exec dbms_redefinition.abort_redef_table('<스키마명>','<테이블명>','INT_<테이블명>');