테스트 환경 구성

스키마 생성 및 권한 부여

create tablesapce TEST datafile '+DATA' size 3G;
create user TEST identified by TEST default tablespace TEST;
grant CONNECT, RESOURCE, UNLIMITED TABLESPACE to TEST;

테스트용 원본 테이블 생성

원본 테이블 생성

create table TEST.YKTEST
(
    BASE_DT     varchar2(8) not null,
    TESTCOL1    varchar2(20) not null,
    TESTCOL2    number not null,
    TESTCOL3    char(20) not null,
    TESTCOL4    varchar2(20),
    TESTCOL5    char(20),
    TESTCOL6    nchar(20),
    TESTCOL7    varchar2(20),
    TESTCOL8    varchar2(20),
    TESTCOL9    varchar2(20),
    constraint PK_YKTEST primary key (BASE_DT,TESTCOL1,TESTCOL2)
)
tablespace USERS; -- 초기 데이터는 USERS에 적재

랜덤 데이터 입력

insert
  into TEST.YKTEST
select to_char(sysdate-dbms_random.value(0,365*7),'RRRRMMDD') as BASE_DT,
       dbms_random.string('A',20) as TEST_COL1,
       dbms_random.value(0,1000)  as TEST_COL2,
       dbms_random.string('A',20) as TEST_COL3,
       dbms_random.string('A',20) as TEST_COL4,
       dbms_random.string('A',20) as TEST_COL5,
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 2
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 4
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 6
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 8
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 10
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 12
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 14
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 16
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 18
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) as TEST_COL6, --20
       dbms_random.string('A',20) as TEST_COL7,
	   dbms_random.string('A',2) ||                                                      -- 2
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 8
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 14
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) as TEST_COL8, --20
       dbms_random.string('A',20) as TEST_COL9
  from DUAL connect by level <= 5000000; -- 메모리 부족 발생시 1000000건으로 5번 수행

테이블 파티셔닝

사전 준비

세션 병렬 처리 설정

alter session enable parallel ddl;
alter session enable parallel dml;
alter session force parallel dml parallel 8;
alter session force parallel ddl parallel 8;
alter session force parallel query parallel 8;
-- cpu_count 파라미터보다 1 적은 숫자로 입력 (최대값)

작업 가능 여부 확인

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST', 'YKTEST',dbms_redefinition.cons_use_pk);
-- PK가 없을 경우 dbms_redefinition.cons_use_rowid 옵션 사용

Interim 파티션 테이블 생성

create table TEST.YKTEST_PT
(
    BASE_DT     varchar2(8) not null,
    TESTCOL1    varchar2(20) not null,
    TESTCOL2    number not null,
    TESTCOL3    char(20) not null,
    TESTCOL4    varchar2(20),
    TESTCOL5    char(20),
    TESTCOL6    nchar(20),
    TESTCOL7    varchar2(20),
    TESTCOL8    varchar2(20),
    TESTCOL9    varchar2(20)
)
partition by range (BASE_DT)
 (
partition YKTEST_PT_201504 values less than ('20150501'),
partition YKTEST_PT_201505 values less than ('20150601'),
partition YKTEST_PT_201506 values less than ('20150701'),
partition YKTEST_PT_201507 values less than ('20150801'),
partition YKTEST_PT_201508 values less than ('20150901'),
partition YKTEST_PT_201509 values less than ('20151001'),
partition YKTEST_PT_201510 values less than ('20151101'),
partition YKTEST_PT_201511 values less than ('20151201'),
partition YKTEST_PT_201512 values less than ('20160101'),
partition YKTEST_PT_201601 values less than ('20160201'),
partition YKTEST_PT_201602 values less than ('20160301'),
partition YKTEST_PT_201603 values less than ('20160401'),
partition YKTEST_PT_201604 values less than ('20160501'),
partition YKTEST_PT_201605 values less than ('20160601'),
partition YKTEST_PT_201606 values less than ('20160701'),
partition YKTEST_PT_201607 values less than ('20160801'),
partition YKTEST_PT_201608 values less than ('20160901'),
partition YKTEST_PT_201609 values less than ('20161001'),
partition YKTEST_PT_201610 values less than ('20161101'),
partition YKTEST_PT_201611 values less than ('20161201'),
partition YKTEST_PT_201612 values less than ('20170101'),
partition YKTEST_PT_201701 values less than ('20170201'),
partition YKTEST_PT_201702 values less than ('20170301'),
partition YKTEST_PT_201703 values less than ('20170401'),
partition YKTEST_PT_201704 values less than ('20170501'),
partition YKTEST_PT_201705 values less than ('20170601'),
partition YKTEST_PT_201706 values less than ('20170701'),
partition YKTEST_PT_201707 values less than ('20170801'),
partition YKTEST_PT_201708 values less than ('20170901'),
partition YKTEST_PT_201709 values less than ('20171001'),
partition YKTEST_PT_201710 values less than ('20171101'),
partition YKTEST_PT_201711 values less than ('20171201'),
partition YKTEST_PT_201712 values less than ('20180101'),
partition YKTEST_PT_201801 values less than ('20180201'),
partition YKTEST_PT_201802 values less than ('20180301'),
partition YKTEST_PT_201803 values less than ('20180401'),
partition YKTEST_PT_201804 values less than ('20180501'),
partition YKTEST_PT_201805 values less than ('20180601'),
partition YKTEST_PT_201806 values less than ('20180701'),
partition YKTEST_PT_201807 values less than ('20180801'),
partition YKTEST_PT_201808 values less than ('20180901'),
partition YKTEST_PT_201809 values less than ('20181001'),
partition YKTEST_PT_201810 values less than ('20181101'),
partition YKTEST_PT_201811 values less than ('20181201'),
partition YKTEST_PT_201812 values less than ('20190101'),
partition YKTEST_PT_201901 values less than ('20190201'),
partition YKTEST_PT_201902 values less than ('20190301'),
partition YKTEST_PT_201903 values less than ('20190401'),
partition YKTEST_PT_201904 values less than ('20190501'),
partition YKTEST_PT_201905 values less than ('20190601'),
partition YKTEST_PT_201906 values less than ('20190701'),
partition YKTEST_PT_201907 values less than ('20190801'),
partition YKTEST_PT_201908 values less than ('20190901'),
partition YKTEST_PT_201909 values less than ('20191001'),
partition YKTEST_PT_201910 values less than ('20191101'),
partition YKTEST_PT_201911 values less than ('20191201'),
partition YKTEST_PT_201912 values less than ('20200101'),
partition YKTEST_PT_202001 values less than ('20200201'),
partition YKTEST_PT_202002 values less than ('20200301'),
partition YKTEST_PT_202003 values less than ('20200401'),
partition YKTEST_PT_202004 values less than ('20200501'),
partition YKTEST_PT_202005 values less than ('20200601'),
partition YKTEST_PT_202006 values less than ('20200701'),
partition YKTEST_PT_202007 values less than ('20200801'),
partition YKTEST_PT_202008 values less than ('20200901'),
partition YKTEST_PT_202009 values less than ('20201001'),
partition YKTEST_PT_202010 values less than ('20201101'),
partition YKTEST_PT_202011 values less than ('20201201'),
partition YKTEST_PT_202012 values less than ('20210101'),
partition YKTEST_PT_202101 values less than ('20210201'),
partition YKTEST_PT_202102 values less than ('20210301'),
partition YKTEST_PT_202103 values less than ('20210401'),
partition YKTEST_PT_202104 values less than ('20210501'),
partition YKTEST_PT_202105 values less than ('20210601'),
partition YKTEST_PT_202106 values less than ('20210701'),
partition YKTEST_PT_202107 values less than ('20210801'),
partition YKTEST_PT_202108 values less than ('20210901'),
partition YKTEST_PT_202109 values less than ('20211001'),
partition YKTEST_PT_202110 values less than ('20211101'),
partition YKTEST_PT_202111 values less than ('20211201'),
partition YKTEST_PT_202112 values less than ('20220101'),
partition YKTEST_PT_202201 values less than ('20220201'),
partition YKTEST_PT_202202 values less than ('20220301'),
partition YKTEST_PT_202203 values less than ('20220401'),
partition YKTEST_PT_202204 values less than ('20220501'),
partition YKTEST_PT_202205 values less than ('20220601'),
partition YKTEST_PT_202206 values less than ('20220701'),
partition YKTEST_PT_202207 values less than ('20220801'),
partition YKTEST_PT_202208 values less than ('20220901'),
partition YKTEST_PT_202209 values less than ('20221001'),
partition YKTEST_PT_202210 values less than ('20221101'),
partition YKTEST_PT_202211 values less than ('20221201'),
partition YKTEST_PT_202212 values less than ('20230101')
)
tablespace TEST
nologging;
-- 처리 속도를 위해 nologging 옵션 필수, not null 제약조건 포함(기검증 데이터 적재로 속도 차이 거의 없음)

Redefinition 수행

Redefinition 시작

exec DBMS_REDEFINITION.START_REDEF_TABLE('TEST','YKTEST','YKTEST_PT');
-- Interim 파티션 테이블에 데이터 적재 시작

작업 모니터링

select * from V$ONLINE_REDEF;

데이터 동기화

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST','YKTEST','YKTEST_PT');
-- 온라인 작업으로 중간에 데이터 변경이 많을 경우 수행

PK 제약조건 생성

create unique index TEST.PK_YKTEST_PT on TEST.YKTEST_PT (BASE_DT,TESTCOL1,TESTCOL2) local parallel 8 nologging;
alter table TEST.YKTEST_PT add constraint PK_YKTEST_PT primary key (BASE_DT,TESTCOL1,TESTCOL2) using index;
-- 데이터 동기화 및 검증을 위해 PK 생성이 먼저 필요하며, 수동으로 생성하지 않을 경우 시스템이 동기화를 위해 임의로 PK를 생성해버림
-- 동일한 이름의 PK 인덱스를 사용할 수 없어서, 임시로 "파티션 PK 인덱스명"으로 생성 (작업 종료 시점에 rename 예정)

기타 제약조건 복사

set serveroutput on
--
-- copy_indexes = no => 인덱스는 파티션으로 수동 생성해야 하므로 제외
-- copy_trigger = yes
-- copy_constraints = yes
-- copy_privileges=yes
-- ignore_error = yes
-- copy_statistics = no
--
DECLARE
 num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST','YKTEST','YKTEST_PT',0,TRUE,TRUE,TRUE,TRUE,num_errors,FALSE);
  dbms_output.put_line('Error : '||num_errors);
end;
/

set serveroutput on
DECLARE
 num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  uname=>'TEST',
  orig_table=>'YKTEST',
  int_table=>'YKTEST_PT',
  copy_indexes=>0,
  copy_triggers=>TRUE,
  copy_constraints=>TRUE,
  copy_privileges=>TRUE,
  ignore_errors=>TRUE,
  num_errors=>num_errors,
  copy_statistics=>FALSE);
  dbms_output.put_line('Error : '||num_errors);
end;
/
-- PK와 Not Null 조건이 이미 생성되어 오류 건수 검출 예정

에러 확인

select object_name, base_table_name, ddl_txt from  DBA_REDEFINITION_ERRORS;

PK 관련 이름 변경

alter index TEST.PK_YKTEST rename to PK_YKTEST_BK;
alter index TEST.PK_YKTEST_PT rename to PK_YKTEST;
alter index TEST.PK_YKTEST logging;
alter table TEST.YKTEST rename constraint PK_YKTEST to PK_YKTEST_BK;
alter table TEST.YKTEST_PT rename constraint PK_YKTEST_PT to PK_YKTEST;

작업 종료

Redefiniton 종료

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','YKTEST','YKTEST_PT');

파티션 테이블 모드 변경

alter table TEST.YKTEST_PT logging;

실패시 정리 방법

exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('TEST','YKTEST','YKTEST_PT');

Interim 테이블 가공을 통해 테이블 구조 변경 가능 (interim 테이블 : 작업 단계에서 데이터를 적재하는 테이블로, 최종 단계에서 원본 테이블로 교체됨)
: 컬럼 순서 변경 가능. 동일한 컬럼명 사용 필수
: 컬럼 크기 확장 가능. 컬럼 크기 축소 불가능 ex) varchar2(20) => varchar2(40)
START_REDEF_TABLE 수행시 MLOG$_<테이블명>, RUPD$_<테이블명>의 테이블이 생성되나 세그먼트는 할당되지 않음
: 원본 테이블과 interim 테이블간의 동기화를 위해 변경 데이터를 체크하기 위한 용도로 생성됨
START_REDEF_TABLE 이후에 insert된 데이터는 임시 세그먼트에 저장됨
: 파티션 테이블을 새로운 테이블스페이스에 만들었더라도, 임시 세그먼트는 기존 테이블이 있던 테이블스페이스에 임시로 저장됨
: SYNC_INTERIM_TABLE를 수행하면 임시 세그먼트에 있던 내용이 파티션 테이블(interim 테이블)로 옮겨 저장됨
START_REDEF_TABLE 이후 데이터 변경이 많지 않다면 SYNC_INTERIM_TABLE 없이 FINISH_REDEF_TABLE를 수행하면 시간 절약 가능
: SYNC_INTERIM_TABLE와 FINISH_REDEF_TABLE를 수행할 때마다 변경 데이터를 검증하는데 시간이 소요되는 것으로 보임
반드시 PK를 local partition 인덱스로 수동 생성해야 함. PK 제약조건 또한 수동으로 추가하는 것을 권장함
: COPY_TABLE_DEPENDENTS 로 인덱스를 복제할 경우, 기존 테이블의 인덱스 구조로 파티션이 안된 인덱스가 생성됨
: PK 인덱스만 파티션으로 수동으로 생성하고, COPY_TABLE_DEPENDENTS 로 PK 제약조건을 복사하면 FINISH_REDEF_TABLE 단계에서 인덱스와 제약조건의 이름이 같지 않아 오류가 발생함
: 오류 예방을 최소화하고자 PK 작업은 모두 수동으로 하는 것을 권장함