I. 테스트 환경 구성

1. 스키마 생성 및 권한 부여

테스트 스키마 생성 및 권한 부여
create tablesapce TEST datafile '+DATA' size 3G;
create user TEST identified by TEST default tablespace TEST;
grant CONNECT, RESOURCE, UNLIMITED TABLESPACE to TEST;

2. 테스트용 원본 테이블 생성

A) 원본 테이블 생성

테스트용 원본 테이블 생성
create table TEST.DPTEST
(
    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_DPTEST primary key (BASE_DT,TESTCOL1,TESTCOL2)
);

B) 랜덤 데이터 입력

랜덤 데이터 입력
insert
  into TEST.DPTEST
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번 수행

II. 테이블 파티셔닝

1. 사전 준비

A) 디렉토리 객체 생성

디렉토리 생성 및 권한 부여
create directory dpump as '/home/oracle/datapump';
grant read, write on directory dpump to system;

B) 데이터 펌프로 데이터 추출

expdp로 데이터 추출
expdp system/oracle directory=dpump dumpfile=DPTEST_%U.dmp logfile=DPTEST_exp.log job_name=EXP_DPTEST filesize=30G parallel=2 tables=TEST.DPTEST

2. 테이블 재생성 및 데이터 적재

A) 테이블 삭제

원본 테이블 삭제
drop table TEST.DPTEST;

B) 파티션 테이블 생성

파티션 테이블 생성
create table TEST.DPTEST
(
    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)
)
tablespace PT_TEST
nologging
partition by range (BASE_DT)
(
partition DPTEST_P201504 values less than ('20150501'),
partition DPTEST_P201505 values less than ('20150601'),
partition DPTEST_P201506 values less than ('20150701'),
partition DPTEST_P201507 values less than ('20150801'),
partition DPTEST_P201508 values less than ('20150901'),
partition DPTEST_P201509 values less than ('20151001'),
partition DPTEST_P201510 values less than ('20151101'),
partition DPTEST_P201511 values less than ('20151201'),
partition DPTEST_P201512 values less than ('20160101'),
partition DPTEST_P201601 values less than ('20160201'),
partition DPTEST_P201602 values less than ('20160301'),
partition DPTEST_P201603 values less than ('20160401'),
partition DPTEST_P201604 values less than ('20160501'),
partition DPTEST_P201605 values less than ('20160601'),
partition DPTEST_P201606 values less than ('20160701'),
partition DPTEST_P201607 values less than ('20160801'),
partition DPTEST_P201608 values less than ('20160901'),
partition DPTEST_P201609 values less than ('20161001'),
partition DPTEST_P201610 values less than ('20161101'),
partition DPTEST_P201611 values less than ('20161201'),
partition DPTEST_P201612 values less than ('20170101'),
partition DPTEST_P201701 values less than ('20170201'),
partition DPTEST_P201702 values less than ('20170301'),
partition DPTEST_P201703 values less than ('20170401'),
partition DPTEST_P201704 values less than ('20170501'),
partition DPTEST_P201705 values less than ('20170601'),
partition DPTEST_P201706 values less than ('20170701'),
partition DPTEST_P201707 values less than ('20170801'),
partition DPTEST_P201708 values less than ('20170901'),
partition DPTEST_P201709 values less than ('20171001'),
partition DPTEST_P201710 values less than ('20171101'),
partition DPTEST_P201711 values less than ('20171201'),
partition DPTEST_P201712 values less than ('20180101'),
partition DPTEST_P201801 values less than ('20180201'),
partition DPTEST_P201802 values less than ('20180301'),
partition DPTEST_P201803 values less than ('20180401'),
partition DPTEST_P201804 values less than ('20180501'),
partition DPTEST_P201805 values less than ('20180601'),
partition DPTEST_P201806 values less than ('20180701'),
partition DPTEST_P201807 values less than ('20180801'),
partition DPTEST_P201808 values less than ('20180901'),
partition DPTEST_P201809 values less than ('20181001'),
partition DPTEST_P201810 values less than ('20181101'),
partition DPTEST_P201811 values less than ('20181201'),
partition DPTEST_P201812 values less than ('20190101'),
partition DPTEST_P201901 values less than ('20190201'),
partition DPTEST_P201902 values less than ('20190301'),
partition DPTEST_P201903 values less than ('20190401'),
partition DPTEST_P201904 values less than ('20190501'),
partition DPTEST_P201905 values less than ('20190601'),
partition DPTEST_P201906 values less than ('20190701'),
partition DPTEST_P201907 values less than ('20190801'),
partition DPTEST_P201908 values less than ('20190901'),
partition DPTEST_P201909 values less than ('20191001'),
partition DPTEST_P201910 values less than ('20191101'),
partition DPTEST_P201911 values less than ('20191201'),
partition DPTEST_P201912 values less than ('20200101'),
partition DPTEST_P202001 values less than ('20200201'),
partition DPTEST_P202002 values less than ('20200301'),
partition DPTEST_P202003 values less than ('20200401'),
partition DPTEST_P202004 values less than ('20200501'),
partition DPTEST_P202005 values less than ('20200601'),
partition DPTEST_P202006 values less than ('20200701'),
partition DPTEST_P202007 values less than ('20200801'),
partition DPTEST_P202008 values less than ('20200901'),
partition DPTEST_P202009 values less than ('20201001'),
partition DPTEST_P202010 values less than ('20201101'),
partition DPTEST_P202011 values less than ('20201201'),
partition DPTEST_P202012 values less than ('20210101'),
partition DPTEST_P202101 values less than ('20210201'),
partition DPTEST_P202102 values less than ('20210301'),
partition DPTEST_P202103 values less than ('20210401'),
partition DPTEST_P202104 values less than ('20210501'),
partition DPTEST_P202105 values less than ('20210601'),
partition DPTEST_P202106 values less than ('20210701'),
partition DPTEST_P202107 values less than ('20210801'),
partition DPTEST_P202108 values less than ('20210901'),
partition DPTEST_P202109 values less than ('20211001'),
partition DPTEST_P202110 values less than ('20211101'),
partition DPTEST_P202111 values less than ('20211201'),
partition DPTEST_P202112 values less than ('20220101'),
partition DPTEST_P202201 values less than ('20220201'),
partition DPTEST_P202202 values less than ('20220301'),
partition DPTEST_P202203 values less than ('20220401'),
partition DPTEST_P202204 values less than ('20220501'),
partition DPTEST_P202205 values less than ('20220601'),
partition DPTEST_P202206 values less than ('20220701'),
partition DPTEST_P202207 values less than ('20220801'),
partition DPTEST_P202208 values less than ('20220901'),
partition DPTEST_P202209 values less than ('20221001'),
partition DPTEST_P202210 values less than ('20221101'),
partition DPTEST_P202211 values less than ('20221201'),
partition DPTEST_P202212 values less than ('20230101')
);

C) 데이터 적재

impdp로 데이터 적재
impdp system/oracle directory=dpump dumpfile=DPTEST_%U.dmp logfile=DPTEST_imp.log job_name=IMP_DPTEST parallel=2 content=DATA_ONLY

III. 정리 작업

1. 파티션 인덱스 및 PK 제약조건 추가

A) 파티션 인덱스 생성

Local 인덱스 생성
create unique index TEST.PK_DPTEST on TEST.DPTEST (BASE_DT,TESTCOL1,TESTCOL2) local parallel 2 nologging;

B) PK 생성

Local 파티션 인덱스를 사용하는 PK 추가
alter table TEST.DBTEST_PT add constraint PK_DPTEST_PT primary key (BASE_DT,TESTCOL1,TESTCOL2) using index;

2. 테이블 인덱스 속성 원복

A) 테이블 속성 원복

테이블 로깅 옵션 원복
alter table TEST.DPTEST logging;

B) 인덱스 속성 원복

병렬 처리 및 로깅 옵션 원복
alter index TEST.PK_DPTEST logging;
alter index TEST.PK_DPTEST noparallel;


기술 메모

  1. 원복을 위해 원본 테이블을 drop하는 것이 부담될 경우, 용량이 허용된다면 rename 옵션을 사용할 수 있음
    : 변환 시나리오 중 유일하게 원본 테이블을 drop 또는 rename 하는 과정이 있어서 충분한 다운타임 확보가 필요
  2. 데이터 펌프의 필터링 조건을 이용하면 원하는 데이터만 추출/적재할 수 있음
    : 테이블에 조건에 해당되는 인덱스가 있을 경우, 처리 속도를 감안하여 미리 대상을 정하여 추출하는 것을 권장
    : 적재시에 조건을 사용할 경우, 인덱스가 없는 덤프 파일 전체의 데이터를 검토해야 하므로 성능 저하 예상


  • 레이블 없음