-1-
Reviewed by Oracle Certified Master Korea Community
( http://www.ocmkorea.com http://cafe.daum.net/oraclemanager )
1.1.1 파티션 테이블에서 사용할수 있는 리오그 방법
파티션 level 의 import/export 방법을 이용해 파티션 테이블중 특정 파티션( 혹은 서브파티션) 만을
선택적으로 리오그 할수 있다.
작업절차는 다음과 같다.
(1) 리오그 대상 파티션 조회
(2) 파티션 EXPORT
(3) CHILD TABLE CHECK 및 FK 삭제
(4) PARTITION TRUNCATE
(5) Global Index 삭제
(6) 파티션 next 값 조정
(7) 파티션 import
(8) 파티션 next 값 원상복구
(9) Global Index 생성
(10) CHILD TABLE FK 연결
(11) 결과 확인
주) 위 (3), (10) 번은 CHILD TABLE 이 있을 경우만 작업한다.
파티션을 truncate 할경우 해당 테이블의 Local Index 를 제외한 모든 Global
Nonpartitioned Indexes, Global Partitioned indexes 들이 UNUSABLE 상태가 된다. 따
라서 데이량이 많은 테이블 ( 특히 인덱스들이 많은 테이블, tbbhdz01, tbjgba20,
tbjgba12,tbbhba01, tbbhba02, tbjgaz10, tbjgaz40, tbbmbb03 등 ) 은 주의해야 한다.
위의 작업 절차는 파티션의 종류( RANGE,HASH,COMPOSITE )에 상관없이 모두 동일하다. 다만 위절차
상 (4)번째 작업시 RANGE 나 COMPOSITE 의 경우는 PARTITION LEVEL 에서 NEXT 값을 변경 해야하고,
HASH 파티션의 경우는 PARTITION LEVEL 에서 변경이 불가능 하므로 테이블 LEVEL 에서 NEXT 값을 변경
해야 한다.
또한 COMPOSITE 파티션의 경우 SUBPARTITION LEVEL 에서도 리오그 작업이 가능하지만 실제로 이용할
경우는 없을 것이다. 왜냐하면, PARTITION LEVEL 에서 SUBPARTITION 개수를 지정해줄 때 모두 4 개로
지정 했기 때문에 특정 파티션에 속하는 모든 SUBPARTITION 들은 모두 거의 동일한 테이타량을 포함
하고 있기 때문이다.

-2-
참고) 해쉬 알고리즘을 적용 받는 파티션 테이블들의 경우 만약 파티션 개수 (혹은 서브파티션 개
수)가 2 의 N 승으로(2,4,6,8,16…) 구성될 경우 모든 파티션( 혹은 서브파티션)들에 대해 거의 동일
하게 테이타를 분배 시킨다.
1.1.1.1 테이블 파티션 리오그
[단계 1] 리오그 대상 파티션을 조회한다.

-3-
$ sqlplus
SQL*Plus: Release 8.1.6.0.0 - Production on Wed Feb 14 17:47:50 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter user-name: nhicadm
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
With the Partitioning option
JServer Release 8.1.6.3.0 - Production
10.1.60.6 csuo002 csuo002. loghost
USER is "NHICADM"
SQL> col segment_name format a15
SQL> col partition_name format a15
SQL> col tablespace_name format a15
SQL> select segment_name, partition_name, extents,(bytes/1024)/1024 Msize
,(next_extent/1024)/1024 Minit,(next_extent/1024)/1024 Mnext, min_extents
2 from user_segments
3 where segment_name = 'TBJGBA12'
4 and extents > 30; .. 리오그 대상 extent 개수
SEGMENT_NAME PARTITION_NAME EXTENTS MSIZE MINIT MNEXT MIN_EXTENTS
--------------- --------------- ---------- ---------- ---------- ---------- ------------
TBJGBA12 SYS_P4561 1 60 60 1 1
……………………………………………….…….중략………………..……………
………………………….
TBJGBA12 SYS_P4564 43 102.65625 60 1 1
……………………………………………….…….중략………………..……………
………………………….
TBJGBA12 SYS_P4570 1 60 60 1 1
.. 파티션 SYS_P4564 를 리오그해 보자.
[단계 2] 리오그 대상 파티션 EXPORT

-4-
$exp nhicadm/ passwd file=sys_p4564.dmp tables=tbjgba12:sys_p4564 indexes=n constraints=n
log=exp.log
Export: Release 8.1.6.3.0 - Production on Tue Mar 6 17:11:38 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
With the Partitioning option
JServer Release 8.1.6.3.0 - Production
Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified tables via Conventional Path ...
. . exporting table TBJGBA12
. . exporting partition SYS_P4564 660032 rows exported
Export terminated successfully without warnings.
작업자 실수를 대비해 table 전체를 다른 이름으로 다른 DEVICE 에 중복되게 EXPORT 를
받아 놓도록한다. ( export 파일이 손상되었을 경우나, 작업자가 다른 파티션을
truncate 하는 등의 실수를 대비해..)
[단계 3] 해당 테이블을 parent 로 하는 FK 가 있는지 찾아낸다. 만약 있다면 해당 테이블은 child

-5-
를 가지고 있으므로 truncate 되지 않는다. 만약 가지고 있다면 해당 FK 를 먼저 DROP 해야 하고 나
중에 import 가 끝난후 다시 FK 를 create 해야 한다.
$ sqlplus
SQL*Plus: Release 8.1.6.0.0 - Production on Tue Mar 6 17:25:52 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter user-name: nhicadm
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
With the Partitioning option
JServer Release 8.1.6.3.0 - Production
10.1.60.6 csuo002 csuo002. loghost
USER is "NHICADM"
SQL> col constraint_name format a35
SQL> col column_name format a25
SQL> select b.constraint_name, b.column_name, b.position
2 from user_constraints a, user_cons_columns b
3 where a.constraint_name=b.constraint_name
4 and a.r_constraint_name='PK_TBJGBA12'
5 and a.constraint_type='R';
ONSTRAINT_NAME COLUMN_NAME POSITION
----------------------------------- ------------------------- ----------
FK_TBJGBA11_TBJGBA12_01 JUNG_NO 1
FK_TBJGBA11_TBJGBA12_01 JUNG_SEQ_NO 2
FK_TBJGBA20_TBJGBA12_01 JUNG_NO 1
FK_TBJGBA20_TBJGBA12_01 JUNG_SEQ_NO 2
FK_TBJGCA20_TBJGBA12_01 JUNG_NO 1
FK_TBJGCA20_TBJGBA12_01 JUNG_SEQ_NO 2

-6-
FK_TBJGCB01_TBJGBA12_01 JUNG_NO 1
FK_TBJGCB01_TBJGBA12_01 JUNG_SEQ_NO 2
.. 4 개의 child table 를 가지고 있으므로 fk 를 각각 삭제해야 한다.
SQL> alter table tbjgba11 drop constraint fk_tbjgba11_tbjgba12_01;
Table altered.
SQL> alter table tbjgba20 drop constraint fk_tbjgba20_tbjgba12_01;
Table altered.
SQL> alter table tbjgca20 drop constraint fk_tbjgca20_tbjgba12_01;
Table altered
SQL> alter table tbjgcb01 drop constraint fk_tbjgcb01_tbjgba12_01;
Table altered.
[단계 4] 리오그 대상 파티션을 TRUNCATE 하고 Index 들의 상태를 조회한다.

-7-
SQL> alter table tbjgba12 truncate partition sys_p4546;
Table truncated.
SQL> select index_name, status from user_indexes
2 where table_name='TBJGBA12';
INDEX_NAME STATUS
------------------------------ --------
IX_TBJGBA12_01 UNUSABLE .. 이하 GLOBAL INDEX 모두가 모두 UNUSBLE
IX_TBJGBA12_04 UNUSABLE 상태가 되었다.
IX_TBJGBA12_07 UNUSABLE
IX_TBJGBA12_02 UNUSABLE
IX_TBJGBA12_05 UNUSABLE
IX_TBJGBA12_10 UNUSABLE
IX_TBJGBA12_08 UNUSABLE
IX_TBJGBA12_03 UNUSABLE
IX_TBJGBA12_06 UNUSABLE
IX_TBJGBA12_09 UNUSABLE
IX_TBJGBA12_11 UNUSABLE
PK_TBJGBA12 N/A .. 파티션 인덱스는 아래와 같이
user_ind_partitions 뷰에서 확인해야 한다.
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name='PK_TBJGBA12';
PARTITION_NAME STATUS
------------------------------ --------
SYS_P4561 USABLE
SYS_P4562 USABLE

-8-
SYS_P4563 USABLE
SYS_P4564 USABLE
SYS_P4565 USABLE
SYS_P4566 USABLE
SYS_P4567 USABLE
SYS_P4568 USABLE
SYS_P4569 USABLE
SYS_P4570 USABLE
.. 모든 인덱스 파티션은 유효 하다.
[단계 5] 모든 Global Index 들의 크기 및 구성칼럼을 조회한후 삭제한다.
SQL> col segment_name format a15
SQL> col tablespace_name format a15
SQL> select segment_name, tablespace_name, (bytes/1024)/1024 Msize,
2 (initial_extent/1024)/1024 Minit, min_extents
3 from user_segments
4 where segment_name like 'IX_TBJGBA12%'
5 /
SEGMENT_NAME TABLESPACE_NAME MSIZE MINIT MIN_EXTENTS
--------------- --------------- ---------- ---------- -----------
IX_TBJGBA12_01 MJGBA12INDX99 57.03125 40 1
IX_TBJGBA12_04 MJGBA12INDX99 130 100 1
IX_TBJGBA12_07 MJGBA12INDX99 57.03125 40 1
IX_TBJGBA12_02 MJGBA12INDX99 142 100 1
IX_TBJGBA12_05 MJGBA12INDX99 57.03125 57 1
IX_TBJGBA12_10 MJGBA12INDX99 190 150 1
IX_TBJGBA12_08 MJGBA12INDX99 57.03125 50 1

-9-
IX_TBJGBA12_03 MJGBA12INDX99 84 80 1
IX_TBJGBA12_06 MJGBA12INDX99 141.015625 100 1
IX_TBJGBA12_09 MJGBA12INDX99 152 100 1
IX_TBJGBA12_11 MJGBA12INDX99 225 200 1
.. Global Index 들의 구성 칼럼을 조회한다.
SQL> col column_name format a30
SQL> select index_name, column_name
2 from user_ind_columns
3 where index_name like 'IX_TBJGBA12%'
4 order by index_name, column_position
5 /
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
IX_TBJGBA12_01 FIRM_SYM
IX_TBJGBA12_01 UNIT_FIRM_SYM
IX_TBJGBA12_02 PSTN_BRCH_CD
IX_TBJGBA12_02 PSTN_TYPE
IX_TBJGBA12_03 SANGSIL_BRCH_CD
IX_TBJGBA12_03 SANGSIL_PSTN_TYPE
IX_TBJGBA12_04 CHWIDUK_BRCH_CD
IX_TBJGBA12_04 CHWIDUK_PSTN_TYPE
IX_TBJGBA12_05 NATION_FINANCE_CD
IX_TBJGBA12_06 JUNG_SANGSIL_DT
IX_TBJGBA12_07 FIRM_SYM
IX_TBJGBA12_07 BUSI_OFCE_NO
IX_TBJGBA12_07 GAIBJA_DEPT_CD
IX_TBJGBA12_08 ARMY_NO_EMP_NO

-10-
IX_TBJGBA12_09 JUNG_CHWIDUK_DT
IX_TBJGBA12_10 PSTN_BRCH_CD
IX_TBJGBA12_10 JUNG_SANGSIL_DT
IX_TBJGBA12_11 JUNG_NO
IX_TBJGBA12_11 JUNG_SEQ_NO
IX_TBJGBA12_11 PSTN_BRCH_CD
.. 모든 Global Index 를 삭제한다.
SQL> drop index IX_TBJGBA12_01;
Index dropped.
SQL> drop index IX_TBJGBA12_02;
Index dropped
SQL> drop index IX_TBJGBA12_03;
Index dropped
SQL> drop index IX_TBJGBA12_04;
Index dropped
SQL> drop index IX_TBJGBA12_05;
Index dropped
SQL> drop index IX_TBJGBA12_06;
Index dropped
SQL> drop index IX_TBJGBA12_07;
Index dropped
SQL> drop index IX_TBJGBA12_08;
Index dropped
SQL> drop index IX_TBJGBA12_09;
Index dropped
SQL> drop index IX_TBJGBA12_10;
Index dropped

-11-
SQL> drop index IX_TBJGBA12_11;
Index dropped
[단계 6] 리오그 대상 파티션이 속하는 테이블스페이스의 FREE SPACE 를 조회하고 및 파티션의 NEXT
값을 조정한다.
[단계 1]로부터 조정할 next 값을 구한다.
: 103M ( partition 총크기 ) . 60M ( partition initial 크기 )
.. table 혹은 partition 을 truncate 할 경우 minextents 까지만 남기고 나머지 space 은 반환한다.
.. truncate 된 파티션이 속하는 tablespace 를 조회한다.
SQL> select tablespace_name from user_tab_partitions
2 where table_name='TBJGBA12'
3 and partition_name='SYS_P4564';
TABLESPACE_NAME
------------------------------
MJGBA12DATA04
.. 테이블스페이스가 가지고 있는 free space 를 조회한다.
SQL> conn system/ passwd
Connected.
SQL> select file_id, (bytes/1024)/1024 from dba_free_space
2 where tablespace_name='MJGBA12DATA04';
FILE_ID (BYTES/1024)/1024
---------- -----------------
62 50.9921875
1142 4.9921875

-12-
.. 변경할 next 값의 크기 ( 43M ) 에 해당되는 FREE SPACE 가 있슴을 확인.
만약 연속된 공간이 없으면 테이블스페이스를 COALESCE 해보고 ( ALTER TABLESPACE MJGBA12DATA04
COALESCE ) 그래도 연속된 공간이 없으면 테이블 스페이스를 늘려준다.( 단원 4.1 참조 )
SQL> conn nhicadm/ passwd
Connected.
SQL> alter table tbjgba12 storage ( next 43 M );
Table altered.
.. tbjgba12 가 hash partition 이므로 앞에서 살펴 본것과 같이 테이블 level 에서 next 값을
조정한다. 만약 range,composite 일 경우 다음과 같이 파티션 level 에서 next 값을 조정하도록한다.
( alter table 테이블명 modify partition 파티션명 storage ( next ?M ) )
[단계 7] 파티션을 IMPORT 한다.
$ imp system/ passwd file=sys_p4564.dmp tables=tbjgba12:sys_subp4564 fromuser=nhicadm
touser=nhicadm buffer=1048576 ignore=y indexes=n constraints=n log=exp.log
Import: Release 8.1.6.3.0 . Production on Mon Feb 19 11:10:22 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 . Production
With the Partitioning option
Jserver Release 8.1.6.3.0 . Production
Export file created by EXPORT:V08.01.06 via conventional path
import done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
. importing TEMPUSER's objects into TEMPUSER
. . importing partition "TBJGBA12":"SYS_P4564" 660032 rows imported
Import terminated successfully without warnings.
.. import 된 total rows 들이 export 시와 동일한지 살펴본다.
[단계 8] 파티션의 NEXT 값을 원래대로 바꿔 놓는다.

-13-
SQL> alter table tbjgba12 storage ( next 1M );
Table altered.
.. tbjgba12 가 hash partition 이므로 앞에서 살펴 본것과 같이 테이블 level 에서 next 값을
조정한다. 만약 range,composite 일 경우 다음과 같이 파티션 level 에서 next 값을 조정하도록한다.
( alter table 테이블명 modify partition 파티션명 storage ( next ?M ) )
[단계 9] Global Index 들을 생성한다.
.. [단계 5]로부터 구한 global index 들의 정보와 아래의 free space 정보를 이용해 모든 global
index 들을 생성한다.
SQL> conn system/manager
Connected.
SQL> select file_id, (bytes/1024)/1024 from dba_free_space
2 where tablespace_name='MJGBA12INDX99'; .. GLOBAL INDEX 들이 속하는 테이블스페이스
FILE_ID (BYTES/1024)/1024
---------- -----------------
79 1998.945313
734 1998.9921875
761 66.8984375
1019 77.9921875
.. 만약 연속된 공간이 없으면 테이블스페이스를 COALESCE 해보고 ( ALTER TABLESPACE
MJGBA12DATA04 COALESCE ) 그래도 모든 인덱스들의 INITIAL 에 해당 되는 연속된 공간이 부족하면
EXTENT 가 최소로 발생할수 있도록 INITIAL, NEXT 값을 적절히 지정해준다.
.. GLOBAL INDEX 를 생성한다.

-14-
양이 많으므로 script file 를 작성 해서 작업한다.
SQL> conn nhicadm/ passwd
Connected.
create index IX_TBJGBA12_01 on TBJGBA12 (FIRM_SYM asc, UNIT_FIRM_SYM asc)
tablespace mjgba12indx99
storage ( initial 57M next 2M pctincrease 0 )
nologging .. logging 하지 않음
compute statistics .. 통계정보 생성
/
create index IX_TBJGBA12_02 on TBJGBA12 (PSTN_BRCH_CD asc, PSTN_TYPE asc)
tablespace mjgba12indx99
storage ( initial 142M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_03 on TBJGBA12 (SANGSIL_BRCH_CD asc, SANGSIL_PSTN_TYPE
asc)
tablespace mjgba12indx99
storage ( initial 84M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_04 on TBJGBA12 (CHWIDUK_BRCH_CD asc, CHWIDUK_PSTN_TYPE
asc)
tablespace mjgba12indx99

-15-
storage ( initial 130M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_05 on TBJGBA12 (NATION_FINANCE_CD asc)
tablespace mjgba12indx99
storage ( initial 57M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_06 on TBJGBA12 (JUNG_SANGSIL_DT asc)
tablespace mjgba12indx99
storage ( initial 141M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_07 on TBJGBA12 (FIRM_SYM asc, BUSI_OFCE_NO asc, GAIBJA_
DEPT_CD asc)
tablespace mjgba12indx99
storage ( initial 57M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_08 on TBJGBA12 (ARMY_NO_EMP_NO asc)
tablespace mjgba12indx99

-16-
storage ( initial 57M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_09 on TBJGBA12 (JUNG_CHWIDUK_DT asc)
tablespace mjgba12indx99
storage ( initial 152M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_10 on TBJGBA12 (PSTN_BRCH_CD asc, JUNG_SANGSIL_DT asc)
tablespace mjgba12indx99
storage ( initial 190M next 2M pctincrease 0)
nologging
compute statistics
/
create index IX_TBJGBA12_11 on TBJGBA12 (JUNG_NO asc, JUNG_SEQ_NO asc, PSTN_BRCH
_CD asc )
tablespace mjgba12indx99
storage ( initial 225M next 2M pctincrease 0)
nologging
compute statistics
/
.. 인덱스가 많으므로 4 ~ 5 파일 정도로 나누어서 동시에 수행 시키도록한다.
[단계 10] Child Table 들의 FK 를 맺어준다.

-17-
.. [단계 3]에서 삭제한 child table 들의 FK 들을 NOVALIDATE 옵션으로 다시 맺어 준다.
SQL> alter table tbjgba11 add constraint fk_tbjgba11_tbjgba12_01 Foreign key ( jung_no,
jung_seq_no ) references tbjgba12 ( jung_no, jung_seq_no ) novalidate ;
Table altered.
SQL> alter table tbjgba20 add constraint fk_tbjgba20_tbjgba12_01 Foreign key ( jung_no,
jung_seq_no ) references tbjgba12 ( jung_no, jung_seq_no ) novalidate ;
Table altered
SQL> alter table tbjgca20 add constraint fk_tbjgca20_tbjgba12_01 Foreign key ( jung_no,
jung_seq_no ) references tbjgba12 ( jung_no, jung_seq_no ) novalidate ;
Table altered
SQL> alter table tbjgba20 add constraint fk_tbjgba20_tbjgba12_01 Foreign key ( jung_no,
jung_seq_no ) references tbjgba12 ( jung_no, jung_seq_no ) novalidate ;
Table altered
[단계 11] 결과 확인
.. partition extents 확인
SQL> col segment_name format a15
SQL> select segment_name, partition_name, extents from user_segments
2 where segment_name='TBJGBA12' and partition_name='SYS_P4564';
SEGMENT_NAME PARTITION_NAME EXTENTS
--------------- ------------------------------ ----------
TBJGBA12 SYS_P4564 2
.. index 상태 확인
SQL> select index_name, status from user_indexes where table_name='TBJGBA12';

-18-
INDEX_NAME STATUS
------------------------------ --------
IX_TBJGBA12_01 VALID
IX_TBJGBA12_04 VALID
IX_TBJGBA12_07 VALID
IX_TBJGBA12_02 VALID
IX_TBJGBA12_05 VALID
IX_TBJGBA12_10 VALID
IX_TBJGBA12_08 VALID
IX_TBJGBA12_03 VALID
IX_TBJGBA12_06 VALID
IX_TBJGBA12_09 VALID
IX_TBJGBA12_11 VALID .. 모두 유효하다.
PK_TBJGBA12 N/A
SQL> select partition_name, status from user_ind_partitions
2 where index_name = 'PK_TBJGBA12';
PARTITION_NAME STATUS
------------------------------ --------
SYS_P4561 USABLE
SYS_P4562 USABLE
SYS_P4563 USABLE
SYS_P4564 USABLE
SYS_P4565 USABLE
SYS_P4566 USABLE
SYS_P4567 USABLE
SYS_P4568 USABLE
SYS_P4569 USABLE

-19-
SYS_P4570 USABLE .. 모두 유효함.