반응형
12c 부터 사용가능한 online 옵션에 대해서 사용 후에 인덱스가 따로 rebuild를 하지 않아도 되서 굉장히 편리했습니다.
근데 update indexes라는 옵션이 따로 있어서 차이를 비교해보고 추가적으로 compress 옵션 사용시에 인덱스도 compress 되는지 체크하겠습니다.
출처 : https://docs.oracle.com/database/121/VLDBG/GUID-1D59BD49-CD86-4BFE-9099-D3B8D7FD932A.htm#VLDBG1122
Updating Indexes Automatically
Before discussing the individual maintenance operations for partitioned tables and indexes, it is important to discuss the effects of the UPDATE INDEXES clause that can be specified in the ALTER TABLE statement. By default, many table maintenance operation
docs.oracle.com
(20.7.3.3 Moving a Table Partition or Subpartition Online 부분)
Database Administrator’s Guide
Managing tables includes tasks such as creating tables, loading tables, altering tables, and dropping tables.
docs.oracle.com
위의 URL들에 따르면 update indexes 옵션을 사용하면 DDL 중에도 DML이 사용가능하다고 나와있습니다.
The UPDATE INDEXES clause can only change the storage properties for the global indexes on the table or storage properties for the index partitions of any global partitioned index on the table. If you do not include the UPDATE INDEXES clause, then the changes to the rowids cause the indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes receive an ORA-01502 error. In this case, the indexes on the table must be dropped or rebuilt.
테스트를 통해서 확인해보겠습니다.
테이블은 기존에 있던 테이블로 진행하였습니다.
순서
1.파티션 테이블 생성 (기존에 있던것도 가능)
DEV.ORD_RANGE
CREATE UNIQUE INDEX DEV.ORD_RANGE_PK
ON DEV.ORD_RANGE (ORD_NO,ORD_YM)
LOCAL
CREATE INDEX DEV.ORD_RANGE_X01
ON DEV.ORD_RANGE (ORD_DT,ORD_HMS)
LOCAL
로컬 인덱스는 있기 때문에 생성x
글로벌 인덱스는 없기 떄문에 생성
2.인덱스 생성(로컬,글로벌)
CREATE INDEX DEV.ORD_RANGE_X02
ON DEV.ORD_RANGE (SHOP_NO,UPPER2)
GLOBAL;
3.move partition
이때, update indexes 절 유/무에 따른 차이 보기
--1.update index x/ online x /compress x
alter table dev.ord_range move partition P201201 tablespace MY_DATA;
--2.update index o/ online x /compress x
alter table dev.ord_range move partition P201202 tablespace MY_DATA update indexes;
--3.update index x/ online o /compress x
alter table dev.ord_range move partition P201203 tablespace MY_DATA online;
--4.update index x/ online x /compress o
alter table dev.ord_range move partition P201204 tablespace MY_DATA compress;
--5.update index o/ online o /compress x
alter table dev.ord_range move partition P201205 tablespace MY_DATA online update indexes;
--6.update index o/ online x /compress o
alter table dev.ord_range move partition P201206 tablespace MY_DATA update indexes compress;
--7.update index x/ online o /compress o
alter table dev.ord_range move partition P201207 tablespace MY_DATA online compress;
--8.update index o/ online o /compress o
alter table dev.ord_range move partition P201208 tablespace MY_DATA update indexes compress online;
결과 요약
1.compress는 테이블 compress만 한다. 인덱스는 compress 하지 않음.
2.update indexes 절이 효과가 있음. (online 만 쓴거랑 update indexes 만 쓴거랑 동일함.) global index와 local index가 정상으로 된다.
3.online을 붙이면 global_stats가 NO가 유지. 다른옵션은(옵션 안붙이기,update indexes,compress) YES로 바뀐다.
global_stats : 파티션에 대한 통계가 파티션에 대해 전체적으로 수행되었는지(YES), 또는 기본 하위파티션에 대한 통계로부터 추정되었는지를 나타냄 (NO)
4.인덱스 통계정보가 다시 수집된다.
5.이미 UNUSABLE 상태인 GLOBAL 인덱스는 update indexes 절과 online 절에 상관없이 계속 UNSABLE상태이다. (따로 rebuild를 해줘야 한다)
리오그 전 상태
select index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats from dba_ind_partitions
where index_name in
(
'ORD_RANGE',
'ORD_RANGE_PK',
'ORD_RANGE_X01',
'ORD_RANGE_X02'
)
and partition_name in
(
'P201201'
,'P201202'
,'P201203'
,'P201204'
,'P201205'
,'P201206'
,'P201207'
,'P201208'
)
order by 2,3;
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:08:39 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:08:45 NO
DEV ORD_RANGE_PK P201204 USABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:08:53 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:08:59 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:09:04 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:09:10 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:08:40 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:08:46 NO
DEV ORD_RANGE_X01 P201204 USABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:08:54 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:08:59 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:09:05 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:09:11 NO
select owner,index_name,status,num_rows,last_analyzed,partitioned,degree from dba_indexes where table_name='ORD_RANGE'
owner,index_name,status,num_rows,last_analyzed,partitioned,degree
------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:33:05 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
1.모든 옵션 사용 (update indexes compress online)
DDL 후에도 정상
SQL> alter table dev.ord_range move partition P201208 tablespace MY_DATA update indexes compress online;
Table altered.
Elapsed: 00:00:28.32owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:33:05 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:08:39 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:08:45 NO
DEV ORD_RANGE_PK P201204 USABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:08:53 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:08:59 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:09:04 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:08:40 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:08:46 NO
DEV ORD_RANGE_X01 P201204 USABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:08:54 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:08:59 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:09:05 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO
2.online compress 만 사용
DDL 후에도 정상
SQL> alter table dev.ord_range move partition P201207 tablespace MY_DATA online compress;
Table altered.
Elapsed: 00:00:10.20owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:33:05 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:08:39 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:08:45 NO
DEV ORD_RANGE_PK P201204 USABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:08:53 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:08:59 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:37:42 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:08:40 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:08:46 NO
DEV ORD_RANGE_X01 P201204 USABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:08:54 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:08:59 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:37:43 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO
3.update indexes compress 사용
DDL 후에도 정상
SQL> alter table dev.ord_range move partition P201206 tablespace MY_DATA update indexes compress;
Table altered.
Elapsed: 00:00:18.29owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:33:05 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:08:39 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:08:45 NO
DEV ORD_RANGE_PK P201204 USABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:08:53 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:37:42 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:08:40 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:08:46 NO
DEV ORD_RANGE_X01 P201204 USABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:08:54 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:37:43 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO
4.online update indexes 사용
DDL 후에도 정상
SQL> alter table dev.ord_range move partition P201205 tablespace MY_DATA online update indexes;
Table altered.
Elapsed: 00:00:17.62owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:33:05 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:08:39 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:08:45 NO
DEV ORD_RANGE_PK P201204 USABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:39:20 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:37:42 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:08:40 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:08:46 NO
DEV ORD_RANGE_X01 P201204 USABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:39:21 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:37:43 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO
5.compress 사용
GLOBAL 인덱스와 LOCAL 인덱스 모두 비정상상태로 빠짐.
SQL> alter table dev.ord_range move partition P201204 tablespace MY_DATA compress;
Table altered.
Elapsed: 00:00:03.21owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 UNUSABLE 15000000 2023/04/14 10:33:05 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:08:39 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:08:45 NO
DEV ORD_RANGE_PK P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:39:20 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:37:42 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:08:40 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:08:46 NO
DEV ORD_RANGE_X01 P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:39:21 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:37:43 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO
다음 테스트를 위해 GLOBAL 인덱스는 다시 rebuild 함.
alter index DEV.ORD_RANGE_X02 rebuild;
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:41:10 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
6.online 사용
DDL 후에도 정상
SQL> alter table dev.ord_range move partition P201203 tablespace MY_DATA online;
Table altered.
Elapsed: 00:00:31.13owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:41:10 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:08:39 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:41:39 NO
DEV ORD_RANGE_PK P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:39:20 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:37:42 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:08:40 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:41:40 NO
DEV ORD_RANGE_X01 P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:39:21 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:37:43 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO
7.update indexes 사용
DDL 후에도 정상
SQL> alter table dev.ord_range move partition P201202 tablespace MY_DATA update indexes;
Table altered.
Elapsed: 00:00:08.00owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 VALID 15000000 2023/04/14 10:41:10 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 USABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:42:44 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:41:39 NO
DEV ORD_RANGE_PK P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:39:20 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:37:42 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 USABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:42:44 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:41:40 NO
DEV ORD_RANGE_X01 P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:39:21 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:37:43 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO8.노 옵션
GLOBAL 인덱스와 LOCAL 인덱스 모두 비정상상태로 빠짐.
SQL> alter table dev.ord_range move partition P201201 tablespace MY_DATA;
Table altered.
Elapsed: 00:00:01.00owner,index_name,status,num_rows,last_analyzed,partitioned,degree
-------------------------------------------------------------------
DEV ORD_RANGE_PK N/A 15443782 2022/05/03 22:00:42 YES 1
DEV ORD_RANGE_X02 UNUSABLE 15000000 2023/04/14 10:41:10 NO 1
DEV ORD_RANGE_X01 N/A 15165475 2022/05/03 22:00:46 YES 1
index_owner,index_name,partition_name,status,compression,num_rows,last_analyzed,global_stats
------------------------------------------------------------------------------------------------
DEV ORD_RANGE_PK P201201 UNUSABLE DISABLED 625157 2023/04/14 10:34:24 YES
DEV ORD_RANGE_PK P201202 USABLE DISABLED 625594 2023/04/14 10:42:44 YES
DEV ORD_RANGE_PK P201203 USABLE DISABLED 625150 2023/04/14 10:41:39 NO
DEV ORD_RANGE_PK P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:32 YES
DEV ORD_RANGE_PK P201205 USABLE DISABLED 624403 2023/04/14 10:39:20 NO
DEV ORD_RANGE_PK P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_PK P201207 USABLE DISABLED 624361 2023/04/14 10:37:42 NO
DEV ORD_RANGE_PK P201208 USABLE DISABLED 623940 2023/04/14 10:36:24 NO
DEV ORD_RANGE_X01 P201201 UNUSABLE DISABLED 625157 2023/04/14 10:34:44 YES
DEV ORD_RANGE_X01 P201202 USABLE DISABLED 625594 2023/04/14 10:42:44 YES
DEV ORD_RANGE_X01 P201203 USABLE DISABLED 625150 2023/04/14 10:41:40 NO
DEV ORD_RANGE_X01 P201204 UNUSABLE DISABLED 624633 2023/04/14 10:34:47 YES
DEV ORD_RANGE_X01 P201205 USABLE DISABLED 624403 2023/04/14 10:39:21 NO
DEV ORD_RANGE_X01 P201206 USABLE DISABLED 624187 2023/04/14 10:38:35 YES
DEV ORD_RANGE_X01 P201207 USABLE DISABLED 624361 2023/04/14 10:37:43 NO
DEV ORD_RANGE_X01 P201208 USABLE DISABLED 623940 2023/04/14 10:36:25 NO
반응형
'Oracle > 운영' 카테고리의 다른 글
| [ASM] diskgroup에 disk 추가하기 (0) | 2023.05.16 |
|---|---|
| [Single][linux] Oracle 자동 재기동 쉘 작성&등록 (0) | 2023.04.26 |
| [DBMS_SPACE,DBMS_ADVISOR]리오그 대상 확인 빌트인 패키지 비교 (0) | 2023.02.23 |
| 특정 테이블스페이스에서 오브젝트 용량 증가량 조회 (0) | 2023.02.21 |
| Autonomous Health Framework (AHF) 업그레이드 방법 (0) | 2023.02.07 |
댓글