Reviewed by Oracle Certified Master Korea Community ( http:www.ocmkorea.com http:cafe.daum.netoraclemanager ) 1.1.1 파티션테이블에서사용할수있는리오그방법파티션 level 의 importexport 방법을이용해파티션테이블중특정파티션 ( 혹은서브파티션 ) 만을선택적으로리오그할수있다. 작업절차는다음과같다. (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 들이 UN 상태가된다. 따라서데이량이많은테이블 ( 특히인덱스들이많은테이블, 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 들은모두거의동일한테이타량을포함하고있기때문이다. -1-
참고 ) 해쉬알고리즘을적용받는파티션테이블들의경우만약파티션개수 ( 혹은서브파티션개수 ) 가 2 의 N 승으로 (2,4,6,8,16 ) 구성될경우모든파티션 ( 혹은서브파티션 ) 들에대해거의동일하게테이타를분배시킨다. 1.1.1.1 테이블파티션리오그 [ 단계 1] 리오그대상파티션을조회한다. -2-
$ 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,(bytes1024)1024 Msize,(next_extent1024)1024 Minit,(next_extent1024)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 -3-
$exp nhicadmpasswd 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 -4-
를가지고있으므로 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-5-
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 들의상태를조회한다. -6-
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 UN 이하 GLOBAL INDEX 모두가모두 UNUSBLE IX_TBJGBA12_04 UN 상태가되었다. IX_TBJGBA12_07 IX_TBJGBA12_02 IX_TBJGBA12_05 IX_TBJGBA12_10 IX_TBJGBA12_08 IX_TBJGBA12_03 IX_TBJGBA12_06 IX_TBJGBA12_09 IX_TBJGBA12_11 UN UN UN UN UN UN UN UN UN PK_TBJGBA12 NA 파티션인덱스는아래와같이 user_ind_partitions 뷰에서확인해야한다. SQL> select partition_name, status 2 from user_ind_partitions 3 where index_name='pk_tbjgba12'; PARTITION_NAME STATUS ------------------------------ -------- SYS_P4561 SYS_P4562-7-
SYS_P4563 SYS_P4564 SYS_P4565 SYS_P4566 SYS_P4567 SYS_P4568 SYS_P4569 SYS_P4570 모든인덱스파티션은유효하다. [ 단계 5] 모든 Global Index 들의크기및구성칼럼을조회한후삭제한다. SQL> col segment_name format a15 SQL> col tablespace_name format a15 SQL> select segment_name, tablespace_name, (bytes1024)1024 Msize, 2 (initial_extent1024)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-8-
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 IX_TBJGBA12_01 IX_TBJGBA12_02 IX_TBJGBA12_02 IX_TBJGBA12_03 IX_TBJGBA12_03 IX_TBJGBA12_04 IX_TBJGBA12_04 IX_TBJGBA12_05 IX_TBJGBA12_06 IX_TBJGBA12_07 IX_TBJGBA12_07 IX_TBJGBA12_07 IX_TBJGBA12_08 FIRM_SYM UNIT_FIRM_SYM PSTN_BRCH_CD PSTN_TYPE SANGSIL_BRCH_CD SANGSIL_PSTN_TYPE CHWIDUK_BRCH_CD CHWIDUK_PSTN_TYPE NATION_FINANCE_CD JUNG_SANGSIL_DT FIRM_SYM BUSI_OFCE_NO GAIBJA_DEPT_CD ARMY_NO_EMP_NO -9-
IX_TBJGBA12_09 IX_TBJGBA12_10 IX_TBJGBA12_10 IX_TBJGBA12_11 IX_TBJGBA12_11 IX_TBJGBA12_11 JUNG_CHWIDUK_DT PSTN_BRCH_CD JUNG_SANGSIL_DT JUNG_NO JUNG_SEQ_NO PSTN_BRCH_CD 모든 Global Index 를삭제한다. SQL> drop index IX_TBJGBA12_01;. SQL> drop index IX_TBJGBA12_02; SQL> drop index IX_TBJGBA12_03; SQL> drop index IX_TBJGBA12_04; SQL> drop index IX_TBJGBA12_05; SQL> drop index IX_TBJGBA12_06; SQL> drop index IX_TBJGBA12_07; SQL> drop index IX_TBJGBA12_08; SQL> drop index IX_TBJGBA12_09; SQL> drop index IX_TBJGBA12_10; -10-
SQL> drop index IX_TBJGBA12_11; [ 단계 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 systempasswd Connected. SQL> select file_id, (bytes1024)1024 from dba_free_space 2 where tablespace_name='mjgba12data04'; FILE_ID (BYTES1024)1024 ---------- ----------------- 62 50.9921875 1142 4.9921875-11-
변경할 next 값의크기 ( 43M ) 에해당되는 FREE SPACE 가있슴을확인. 만약연속된공간이없으면테이블스페이스를 COALESCE 해보고 ( ALTER TABLESPACE MJGBA12DATA04 COALESCE ) 그래도연속된공간이없으면테이블스페이스를늘려준다.( 단원 4.1 참조 ) SQL> conn nhicadmpasswd 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 systempasswd 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 값을원래대로바꿔놓는다. -12-
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 systemmanager Connected. SQL> select file_id, (bytes1024)1024 from dba_free_space 2 where tablespace_name='mjgba12indx99'; GLOBAL INDEX 들이속하는테이블스페이스 FILE_ID (BYTES1024)1024 ---------- ----------------- 79 1998.945313 734 1998.9921875 761 66.8984375 1019 77.9921875 만약연속된공간이없으면테이블스페이스를 COALESCE 해보고 ( ALTER TABLESPACE MJGBA12DATA04 COALESCE ) 그래도모든인덱스들의 INITIAL 에해당되는연속된공간이부족하면 EXTENT 가최소로발생할수있도록 INITIAL, NEXT 값을적절히지정해준다. GLOBAL INDEX 를생성한다. -13-
양이많으므로 script file 를작성해서작업한다. SQL> conn nhicadmpasswd Connected. create index IX_TBJGBA12_01 on TBJGBA12 (FIRM_SYM asc, UNIT_FIRM_SYM asc) storage ( initial 57M next 2M pctincrease 0 ) logging 하지않음 통계정보생성 create index IX_TBJGBA12_02 on TBJGBA12 (PSTN_BRCH_CD asc, PSTN_TYPE asc) storage ( initial 142M next 2M pctincrease 0) create index IX_TBJGBA12_03 on TBJGBA12 (SANGSIL_BRCH_CD asc, SANGSIL_PSTN_TYPE asc) storage ( initial 84M next 2M pctincrease 0) create index IX_TBJGBA12_04 on TBJGBA12 (CHWIDUK_BRCH_CD asc, CHWIDUK_PSTN_TYPE asc) -14-
storage ( initial 130M next 2M pctincrease 0) create index IX_TBJGBA12_05 on TBJGBA12 (NATION_FINANCE_CD asc) storage ( initial 57M next 2M pctincrease 0) create index IX_TBJGBA12_06 on TBJGBA12 (JUNG_SANGSIL_DT asc) storage ( initial 141M next 2M pctincrease 0) create index IX_TBJGBA12_07 on TBJGBA12 (FIRM_SYM asc, BUSI_OFCE_NO asc, GAIBJA_ DEPT_CD asc) storage ( initial 57M next 2M pctincrease 0) create index IX_TBJGBA12_08 on TBJGBA12 (ARMY_NO_EMP_NO asc) -15-
storage ( initial 57M next 2M pctincrease 0) create index IX_TBJGBA12_09 on TBJGBA12 (JUNG_CHWIDUK_DT asc) storage ( initial 152M next 2M pctincrease 0) create index IX_TBJGBA12_10 on TBJGBA12 (PSTN_BRCH_CD asc, JUNG_SANGSIL_DT asc) storage ( initial 190M next 2M pctincrease 0) create index IX_TBJGBA12_11 on TBJGBA12 (JUNG_NO asc, JUNG_SEQ_NO asc, PSTN_BRCH _CD asc ) storage ( initial 225M next 2M pctincrease 0) 인덱스가많으므로 4 ~ 5 파일정도로나누어서동시에수행시키도록한다. [ 단계 10] Child Table 들의 FK 를맺어준다. -16-
[ 단계 3] 에서삭제한 child table 들의 FK 들을 NOATE 옵션으로다시맺어준다. 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'; -17-
INDEX_NAME STATUS ------------------------------ -------- IX_TBJGBA12_01 IX_TBJGBA12_04 IX_TBJGBA12_07 IX_TBJGBA12_02 IX_TBJGBA12_05 IX_TBJGBA12_10 IX_TBJGBA12_08 IX_TBJGBA12_03 IX_TBJGBA12_06 IX_TBJGBA12_09 IX_TBJGBA12_11 모두유효하다. PK_TBJGBA12 NA SQL> select partition_name, status from user_ind_partitions 2 where index_name = 'PK_TBJGBA12'; PARTITION_NAME STATUS ------------------------------ -------- SYS_P4561 SYS_P4562 SYS_P4563 SYS_P4564 SYS_P4565 SYS_P4566 SYS_P4567 SYS_P4568 SYS_P4569-18-
SYS_P4570 모두유효함. -19-