Oracle® 10g부터 DBMS_REDEFINITION 패키지를 사용하면 애플리케이션 다운타임 없이 온라인 상태에서 테이블을 파티셔닝할 수 있습니다.
이 글에서는 DBMS_REDEFINITION 패키지를 활용해 일반(비파티션) 테이블을 파티션 테이블로 전환하는 전체 과정을 단계별로 살펴봅니다. 예제에서는 비파티션 테이블인 TABLEA를 범위 인터벌(range interval) 파티션 테이블로 변경합니다.
1단계: 기존 비파티션 테이블 백업
아래 명령을 실행하여 TABLEA 테이블의 전체 익스포트(expdp) 백업을 생성합니다.
expdp "/ as sysdba" directory=EXPDP_DIR dumpfile=tableA_UNPAR.dmp logfile=tableA_UNPAR.log TABLES=TEST.TABLEA
expdp "/ as sysdba" directory=EXPDP_DIR dumpfile=tableA_metaunpar.dmp logfile=tableA_metaunpar.log TABLES=TEST.TABLEA content=metadata_only
2단계: 데이터베이스 객체 확인
테이블을 삭제할 때 함께 삭제될 수 있는 종속(D) 객체는 다음과 같습니다.
CONSTRAINT (제약조건) — D
INDEX (인덱스) — D
MATERIALIZED_VIEW_LOG (머터라이즈드 뷰 로그) — D
OBJECT_GRANT (객체 권한) — D
TRIGGER (트리거) — D
아래 SQL을 실행하고 결과를 spool 파일(예: cons_trig_indx.txt)에 저장합니다.
set LINESIZE 500
set PAGESIZE 1000
SQL> spool cons_trig_indx.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';
NAME TYPE OWNER
-------------- -------------- -------
PROC_TABLEA PROCEDURE TEST
TABLEA_TRIGG TRIGGER TEST
PKG_TABLEA PACKAGE BODY TEST
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME
from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME STATUS TABLESPACE_NAME
---------------------------------------------------------------------------
TEST TABLEA_IDX_ID01 TEST TABLEA VALID TABLEA_TBL
TEST TABLEA_IDX_ID04 TEST TABLEA VALID TABLEA_TBL
TEST TABLEA_IDX_PK TEST TABLEA VALID TABLEA_TBL
SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME from dba_objects
where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';
no rows selected
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints
where TABLE_NAME='TABLEA' and owner='TEST';
SQL> spool off
CONSTRAINT_NAME C
------------------ -----
SYS_C002004601 C
SYS_C002004602 C
SYS_C002004603 C
IDX_PK P
FK01 R
3단계: TABLEA의 DDL 추출
파티션 테이블을 생성하기 전에 아래 명령을 실행해 TABLEA의 데이터 정의어(DDL)를 추출하고, 스크립트를 spool 파일 DEF_TABLEA.sql에 저장합니다.
set echo off
set feedback off
set linesize 160
set long 2000000
set pagesize 0
set trims on
column txt format a150 word_wrapped
SQL> spool DEF_TABLEA.sql
SQL> select DBMS_METADATA.GET_DDL('TABLE','TABLEA','TEST') txt FROM dual;
SQL> spool off
4단계: DDL 스크립트 복사
3단계에서 생성한 DDL 스크립트를 복사합니다.
cp DEF_TABLEA.sql DEF_TABLEA_PAR.sql
5단계: 비파티션 테이블의 날짜 데이터 확인
아래 쿼리를 실행해 TABLEA에 저장된 날짜(DT) 값을 확인합니다.
SQL> select * from (select DT from TEST.TABLEA where rownum <15 order by DT DESC);
6단계: DEF_TABLEA_PAR.sql 파일 편집
DEF_TABLEA_PAR.sql 파일을 열어 다음과 같이 수정합니다.
모든 TABLEA를 TABLEA_PAR로 변경합니다.
NOT NULL 등 모든 제약조건 항목을 삭제합니다.
새 테이블스페이스에 테이블이 생성되도록 아래 구문을 추가합니다.
TABLESPACE "TABLEA_TBL_PAR" LOGGING5단계에서 확인한 날짜를 기준으로 파티션 정의를 추가하는 아래 구문을 삽입합니다.
PARTITION BY RANGE(DT) interval (numtoyminterval(1,'MONTH')) (partition TABLEA_2004 values less than (to_date('01/01/2005','DD/MM/YYYY')), partition TABLEA_2005 values less than (to_date('01/01/2006','DD/MM/YYYY')));
수정 후 DEF_TABLEA_PAR.sql 파일은 아래 예시와 같은 형태가 됩니다.
CREATE TABLE "TEST"."TABLEA_PAR"
( "ID" NUMBER(6,0),
"CEID" NUMBER(6,0),
"DT" DATE,
"AMT" NUMBER(14,4),
"RET" NUMBER(14,4),
"CNT" NUMBER(4,0),
"VCNT" NUMBER(4,0),
"EXEDT" DATE,
"LASTUPDBY" VARCHAR2(15),
"VENUM" NUMBER(6,0),
"LASTUPDDT" TIMESTAMP (6))
TABLESPACE "TABLEA_TBL_PAR" LOGGING
PARTITION BY RANGE(DT)
interval (numtoyminterval(1,'MONTH'))
(partition TABLEA_2004 values less than (to_date('01/01/2005','DD/MM/YYYY')),
partition TABLEA_2005 values less than (to_date('01/01/2006','DD/MM/YYYY')));
7단계: 파티션 테이블 생성
아래와 같이 DEF_TABLEA_PAR.sql 스크립트를 실행하여 파티션 테이블을 생성합니다.
SQL> spool DEF_TABLEA_PAR.outp.txt
SQL> @DEF_TABLEA_PAR.sql
Table Created.
SQL> spool off
8단계: 파티션 테이블 검증
아래 명령을 실행해 파티션 테이블을 검증하고 정의된 파티션 목록을 확인합니다.
SQL> spool verify_partition.txt
SQL> select partition_name from DBA_tab_partitions where table_name ='TABLEA_PAR' and table_owner = 'TEST';
SQL> spool off
PARTITION_NAME
-----------------
TABLEA_2004
TABLEA_2005
9단계: 비파티션 테이블 통계 수집
아래 명령을 실행해 원본(비파티션) 테이블의 통계 정보를 수집하고 spool 파일에 저장합니다.
SQL> SPOOL gather_stats.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA',cascade => TRUE);
SQL> spool off
10단계: 재정의 가능 여부 확인
참고: 재정의 패키지를 사용하기 전에 원본 테이블(비파티션)에 기본 키가 반드시 있어야 하는 것은 아닙니다.
아래 명령을 실행해 재정의가 가능한지 확인하고 결과를 spool 파일에 저장합니다.
SQL> spool check_the_redefinition.txt
SQL> EXEC DBMS_Redefinition.can_redef_table ('TEST', 'TABLEA');
SQL> spool off
11단계: 재정의 시작
check_the_redefinition.txt에 오류가 기록되지 않았다면, 아래 장기 실행 명령으로 재정의를 시작합니다.
SQL> spool start_redef_table.txt
SQL>begin
dbms_redefinition.start_redef_table
(
uname => 'TEST',
orig_table => 'TABLEA',
int_table => 'TABLEA_PAR');
end;
/
SQL> spool off
12단계: 재정의 중 테이블스페이스 오류 모니터링
11단계의 재정의 작업 중에 아래 예시와 같은 테이블스페이스 경고가 발생할 수 있습니다.
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.TABLEA_PAR
partition SYS_P42 by 1024 in tablespace TABLEA_TBL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2
위와 유사한 테이블스페이스 오류가 발생하면 다음 순서로 조치합니다.
아래 명령을 실행해 재정의 프로세스를 중단합니다.
SQL> spool abort_redef_table.txt SQL> begin dbms_redefinition.abort_redef_table ( uname => 'TEST', orig_table => 'TABLEA', int_table => 'TABLEA_PAR'); end; / SQL> spool off파티션 테이블과 머터라이즈드 뷰를 삭제합니다.
테이블스페이스 크기를 확장합니다. 이 예제에서는 TABLEA_TBL 테이블스페이스의 크기를 늘려야 합니다.
11단계를 다시 실행합니다.
13단계: 재정의 오류 확인
재정의 프로세스가 성공적으로 완료된 후, 아래 명령을 실행해 오류가 있는지 점검합니다.
SQL> spool copy_table_dependents.txt
SQL> SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'TEST',
orig_table => 'TABLEA',
int_table => 'TABLEA_PAR',
copy_indexes => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
SQL> spool off
재정의가 성공했다면 copy_table_dependents.txt 파일에서 아래와 같은 결과를 확인할 수 있습니다.
l_num_errors=0
PL/SQL procedure successfully completed.
14단계: (선택 사항) 파티션 테이블 재동기화
필요하다면 아래 명령을 실행해 임시(interim) 테이블과 원본 테이블을 재동기화할 수 있습니다.
SQL> spool sync_interim_table.txt
SQL>
BEGIN
DBMS_REDEFINITION.sync_interim_table
(
uname => 'TEST',
orig_table => 'TABLEA',
int_table => 'TABLEA_PAR');
END;
/
SQL> spool off
15단계: 파티션 테이블 통계 수집
아래 명령을 실행해 새로 생성된 파티션 테이블의 통계 정보를 수집합니다.
SQL> spool gather_statistics_par.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA_PAR',cascade => TRUE);
SQL> spool off
16단계: 제약조건 활성화 스크립트 생성
validate 제약조건을 활성화하기 위한 스크립트를 아래 명령으로 준비합니다.
SQL> spool constraint_enable_validate.txt
SET LINESIZE 500
SET PAGESIZE 1000
SQL> select 'alter table' ||' '||OWNER||'.'||TABLE_NAME||' enable validate constraint'||' '||CONSTRAINT_NAME||';' from dba_constraints where TABLE_NAME = 'TABLEA_PAR' and OWNER='TEST';
'ALTERTABLE'||''||OWNER||'.'||TABLE_NAME||'ENABLEVALIDATECONSTRAINT'||''||CONSTR
--------------------------------------------------------------------------------
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;
SQL> spool off
17단계: validate 제약조건 활성화
16단계에서 생성한 스크립트와 명령을 아래 예시처럼 실행합니다.
SQL> spool constraint_enable_execute.outp.txt
SQL>@constraint_enable.sql
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;
SQL> spool off
18단계: 비파티션 테이블과 파티션 테이블 비교
원본 비파티션 테이블과 새로 만든 파티션 테이블을 비교하여 모든 속성이 동일한지 확인합니다.
19단계: 테이블 이름 교체
아래 명령을 실행해 임시 테이블을 실제 테이블로 설정함으로써 두 테이블의 이름을 교체합니다.
SQL> spool finish_redef_table.txt
BEGIN
DBMS_REDEFINITION.finish_redef_table
(
uname => 'TEST',
orig_table => 'TABLEA',
int_table => 'TABLEA_PAR');
END;
/
--------------------------------------------
@?/rdbms/admin/utlrp.sql
--------------------------------------------
SQL>spool off
20단계: 테이블 레코드 건수 비교
아래 명령을 실행해 두 테이블의 레코드 건수를 비교하고 서로 일치하는지 확인합니다.
SQL> spool table_count.outp.txt
SQL> select count(*) from TEST.TABLEA;
COUNT(*)
----------
890540
SQL> select count (*) from TEST.TABLEA_PAR;
COUNT(*)
----------
890540
SQL> spool off
21단계: 파티션 적용 성공 여부 검증
아래 명령을 실행해 파티션이 정상적으로 적용되었는지 확인합니다.
SQL> spool check_partition.txt
SQL> select partitioned from dba_tables where table_name = 'TABLEA' and owner='TEST';
PAR
------
YES
SQL> select partition_name , SUBPARTITION_COUNT, TABLESPACE_NAME from dba_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> select table_name, partition_name, high_value, partition_position from DBA_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> spool off
22단계: 데이터베이스 객체 재확인
아래 명령을 실행해 데이터베이스 객체를 다시 점검하고, 그 결과를 2단계의 출력과 비교합니다.
SET LINESIZE 500
SET PAGESIZE 1000
SQL> spool cons_indx_trigg.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';
NAME TYPE OWNER
---------------- --------------- ------------
PROC_TABLEA PROCEDURE TEST
TABLEA_TRIGG TRIGGER TEST
PKG_TABLEA PACKAGE BODY TEST
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME STATUS TABLESPACE_NAME
------------------------------------------------------------------------
TEST TABLEA_IDX_ID01 TEST TABLEA VALID TABLEA_TBL
TEST TABLEA_IDX_ID04 TEST TABLEA VALID TABLEA_TBL
TEST TABLEA_IDX_PK TEST TABLEA VALID TABLEA_TBL
SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME from dba_objects where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';
no rows selected
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where TABLE_NAME='TABLEA' and owner='TEST';
CONSTRAINT_NAME C
------------------- ----------
SYS_C002004601 C
SYS_C002004602 C
SYS_C002004603 C
IDX_PK P
FK01 R
12 rows selected.
SQL> spool off
23단계: 인덱스 재구축
아래 명령을 실행해 새 테이블스페이스(TABLEA_TBL_PAR)에 인덱스를 ONLINE 모드로 재구축합니다.
SQL> spool rebuild_indx.txt
SQL>@rebuild_index.sql
ALTER INDEX TEST.TABLEA_IDX_ID01 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.ITABLEA_IDX_ID04 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.TABLEA_IDX_PK REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
SQL> spool off
24단계: 인덱스 상태 검증
아래 명령을 실행해 모든 인덱스의 상태가 VALID이고, 테이블스페이스가 TABLEA_TBL_PAR로 지정되어 있는지 확인합니다.
SQL> spool verify_indx.outp.txt
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME STATUS TABLESPACE_NAME
---------------------------------------------------------------------------
TEST TABLEA_IDX_ID01 TEST TABLEA VALID TABLEA_TBL_PAR
TEST TABLEA_IDX_ID04 TEST TABLEA VALID TABLEA_TBL_PAR
TEST TABLEA_IDX_PK TEST TABLEA VALID TABLEA_TBL_PAR
SQL>spool off
25단계: 원본 비파티션 테이블 삭제
DBA가 모든 사항을 확인하고 문제가 없다고 판단하면, 이제 임시 테이블 이름(TEST.TABLEA_PAR)이 된 원본 테이블을 아래 명령으로 삭제합니다.
SQL> DROP table TEST.TABLEA_PAR cascade constraints;
결론
위 단계에서는 임시 테이블 TEST.TABLEA_PAR를 활용해 TEST.TABLEA 테이블을 애플리케이션 다운타임 없이 범위 인터벌(range interval) 파티션 테이블로 전환했습니다.
궁금한 점이나 의견이 있다면 피드백 탭을 통해 남겨 주세요.