내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
10.12
14:00
OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 비파티션 테이블을 파티션 테이블로 온라인 전환 방법
본문에서는 일반 테이블(비파티션 테이블)을 파티션 테이블로 온라인으로 변경하는 방법에 대해 설명함
변경 하는 방법은 몇가지가 존재하지만 본문에서는 12c 부터 사용 가능한 alter table modify online 옵션을 이용해 변경함
온라인 옵션을 사용하기 때문에 lock이 발생하지 않지만 어떤 lmode lock이 발생하는지도 같이 확인해봄
그리고 local, global 인덱스도 함께 생성하여 변경 시 어떻게 동작하는지 확인해봄
UPDATE INDEXES 절 사용시 고려 사항
- 인덱스의 파티셔닝 상태와 저장 속성을 변경하는 데 사용될 수 있음
- UPDATE INDEXES 절의 명시는 선택사항임
- 인덱스는 온라인 및 오프라인 변환 모두에서 유지됨
- 원래 인덱스 목록이 정의된 열을 변경할 수 없음
- 인덱스의 고유성 또는 다른 인덱스 속성을 변경할 수 없음
- 어떤 인덱스에 대해서도 테이블스페이스를 지정하지 않으면 다음과 같은 기본 테이블스페이스가 적용됨
- 변환 후 로컬 인덱스는 테이블 파티션과 함께 배치됨
- 변환 후 글로벌 인덱스는 비파티션 테이블의 원래 글로벌 인덱스와 동일한 테이블스페이스에 위치함
- INDEXES 절을 지정하지 않거나 INDEXES 절에서 원래 비파티션 테이블의 모든 인덱스를 지정하지 않으면 다음과 같은 기본 동작이 모든 미지정된 인덱스에 적용됨
- 글로벌 파티셔닝 인덱스는 동일하게 유지되며 원래의 파티셔닝 구조를 유지함
- Non-prefixed 인덱스는 글로벌 비파티셔닝 인덱스로 변환됨
- prefixed 인덱스는 로컬 파티셔닝 인덱스로 변환됨
- 비트맵 인덱스는 prefixed 여부와 관계없이 로컬 파티셔닝 인덱스로 변환됨
- 비트맵 인덱스는 항상 로컬 파티셔닝 인덱스여야 함
- 도메인 인덱스가 있는 경우 변환 작업을 수행할 수 없음
원문
Considerations When Using the UPDATE INDEXES Clause
- When using the UPDATE INDEXES clause, note the following.
- This clause can be used to change the partitioning state of indexes and storage properties of the indexes being converted.
- The specification of the UPDATE INDEXES clause is optional.
- Indexes are maintained both for the online and offline conversion to a partitioned table.
- This clause cannot change the columns on which the original list of indexes are defined.
- This clause cannot change the uniqueness property of the index or any other index property.
- If you do not specify the tablespace for any of the indexes, then the following tablespace defaults apply.
- Local indexes after the conversion collocate with the table partition.
- Global indexes after the conversion reside in the same tablespace of the original global index on the non-partitioned table.
- If you do not specify the INDEXES clause or the INDEXES clause does not specify all the indexes on the original non-partitioned table, then the following default behavior applies for all unspecified indexes.
- Global partitioned indexes remain the same and retain the original partitioning shape.
- Non-prefixed indexes become global nonpartitioned indexes.
- Prefixed indexes are converted to local partitioned indexes.
- Prefixed means that the partition key columns are included in the index definition, but the index definition is not limited to including the partitioning keys only.
- Bitmap indexes become local partitioned indexes, regardless whether they are prefixed or not.
- Bitmap indexes must always be local partitioned indexes.
- The conversion operation cannot be performed if there are domain indexes.
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5
테스트
샘플 테이블 생성(파티션 테이블)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL>
conn imsi/imsi
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20220100')
);
Table created.
|
파티션 테이블 샘플 데이터 삽입(원하는 만큼 실행)(나는 1..10를 1..100으로 변경해서 실행함)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF PTABLE%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for j in 1..10 loop
FOR i IN 1..100000 LOOP
w_ins(i).col1 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).col2 := ceil(dbms_random.value(1, 10000000));
END LOOP;
FORALL i in 1..100000 INSERT INTO PTABLE VALUES w_ins(i);
COMMIT;
end loop;
END;
/
PL/SQL procedure successfully completed
|
정상적으로 삽입됨
파티션 별 용량 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
SQL>
set lines 200 pages 1000
col owner for a10
col tablespace_name for a15
col segment_name for a15
col partition_name for a15
col segment_type for a15
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
--select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024/1024) gb
from dba_segments
where segment_name = 'PTABLE'
and segment_type='TABLE PARTITION'
order by 3,4;
OWNER TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MB
---------- --------------- --------------- --------------- --------------- ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 16
IMSI USERS PTABLE P202102 TABLE PARTITION 24
IMSI USERS PTABLE P202103 TABLE PARTITION 24
IMSI USERS PTABLE P202104 TABLE PARTITION 24
IMSI USERS PTABLE P202105 TABLE PARTITION 24
IMSI USERS PTABLE P202106 TABLE PARTITION 24
IMSI USERS PTABLE P202107 TABLE PARTITION 24
IMSI USERS PTABLE P202108 TABLE PARTITION 24
IMSI USERS PTABLE P202109 TABLE PARTITION 24
IMSI USERS PTABLE P202110 TABLE PARTITION 24
IMSI USERS PTABLE P202111 TABLE PARTITION 24
IMSI USERS PTABLE P202112 TABLE PARTITION 16
12 rows selected.
|
용량이 파티션당 약 24mb 씩 할당됨
글로벌 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
drop index ptbl_ix01;
create index ptbl_ix01 on ptable(col1) global
partition by range (col1)
(
partition p202101 values less than ('20210000'),
partition p202105 values less than ('20210700'),
partition p202112 values less than ('20220100'),
partition p_max values less than (maxvalue)
);
Index created.
|
로컬 인덱스 생성
|
1
2
3
4
5
|
SQL>
drop index ptbl_ix02;
create index ptbl_ix02 on ptable(col1, col2) local;
Index created.
|
인덱스 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col table_name for a20
col partition_name for a20
select dip.index_owner, dpi.table_name, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE TABLE_NAME INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT
---------- -------------------- -------------------- -------------------- -------- ------ ------------
IMSI PTABLE PTBL_IX01 P202101 USABLE GLOBAL PREFIXED
IMSI PTABLE PTBL_IX01 P202105 USABLE GLOBAL PREFIXED
IMSI PTABLE PTBL_IX01 P202112 USABLE GLOBAL PREFIXED
IMSI PTABLE PTBL_IX01 P_MAX USABLE GLOBAL PREFIXED
IMSI PTABLE PTBL_IX02 P202101 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202102 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202103 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202104 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202105 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202106 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202107 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202108 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202109 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202110 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202111 USABLE LOCAL PREFIXED
IMSI PTABLE PTBL_IX02 P202112 USABLE LOCAL PREFIXED
16 rows selected.
|
정상적으로 생성됨
통계정보 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'PTABLE');
PL/SQL procedure successfully completed.
|
파티션별 row 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col table_name for a10
col partition_name for a20
select table_name, partition_name, num_rows from dba_tab_partitions
where table_name = 'PTABLE'
order by 1, 2;
TABLE_NAME PARTITION_NAME NUM_ROWS
---------- -------------------- ----------
PTABLE P202101 454636
PTABLE P202102 910773
PTABLE P202103 909417
PTABLE P202104 908636
PTABLE P202105 908805
PTABLE P202106 909456
PTABLE P202107 910558
PTABLE P202108 908277
PTABLE P202109 909540
PTABLE P202110 908028
PTABLE P202111 907203
PTABLE P202112 454671
12 rows selected.
|
샘플 파티션 테이블을 이용해 일반 테이블 생성
|
1
2
3
|
SQL>
drop table nptable purge;
create table nptable as select * from ptable;
|
NPTABLE 테이블 구조 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col table_name for a10
col partitioned for a15
select table_name, partitioned from dba_tables
where table_name like '%PTABLE';
TABLE_NAME PARTITIONED
---------- ---------------
PTABLE YES
NPTABLE NO
|
새로 생성한 NPTABLE은 파티션 테이블이 아닌 일반 테이블임
인덱스도 생성(nptbl_ix01 은 global 인덱스, nptbl_ix02 는 비파티션 인덱스)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
drop index index nptbl_ix01;
create index nptbl_ix01 on nptable(col1) global
partition by range (col1)
(
partition p202101 values less than ('20210000'),
partition p202105 values less than ('20210700'),
partition p202112 values less than ('20220100'),
partition p_max values less than (maxvalue)
);
Index created.
SQL>
drop index index nptbl_ix02;
create index nptbl_ix02 on nptable(col1, col2);
Index created.
|
alter table modify online 명령으로 비파티션 테이블을 파티션 테이블로 변경
alter 구문 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL>
set timing on
alter table nptable
modify
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20220100')
) online
update indexes
(
nptbl_ix01 global partition by range (col1)
(
partition p202101 values less than ('20210000'),
partition p202105 values less than ('20210700'),
partition p202112 values less than ('20220100'),
partition p_max values less than (maxvalue)
)
, nptbl_ix02 local
);
Table altered.
Elapsed: 00:00:29.13
|
29초 소요됨
modify 실행중 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
col object_name for a20
col subobject_name for a20
select sid, type, o.object_name, o.subobject_name, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
from v$lock v, dba_objects o
where 1=1
and TYPE in ('TX','TM')
and v.id1 = o.object_id(+)
order by sid, type;
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
397 TM OBJ$ 18 0 3 0 0 0 18
397 TM SYS_JOURNAL_70837 70838 0 6 0 0 1 5302
397 TM NPTABLE 70837 0 3 0 0 1 5301
397 TM SYS_RMTAB$$_H70837 70840 0 6 0 0 1 5304
397 TX 12845061 30089 6 0 0 196 5
397 TX 12845064 30070 6 0 0 196 8
6 rows selected.
|
nptable에 tm lock lmode 3으로 lock이 걸림
nptable 이외에도 몇가지 오브젝트들이 보이는데 다른 온라인 작업처럼 임시 저널(JOURNAL) 테이블을 사용해서 파티션 테이블로 변경하는듯함
이때 다른세션에서 dml 각각 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
--insert
SQL> insert into nptable values ('20210505', 1);
1 row created.
SQL> rollback;
Rollback complete.
--update
SQL> update nptable set col2 = 2540653 where col1 = '20210606' and col2 = 2540653;
1 row updated.
SQL> rollback;
Rollback complete.
--delete
SQL> delete nptable where col1 = '20210606' and col2 = 2540653;
1 row deleted.
SQL> rollback;
Rollback complete.
|
dml이 정상적으로 수행됨
modify 실행중 dml시(rollback 전) lock 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
col object_name for a20
col subobject_name for a20
select sid, type, o.object_name, o.subobject_name, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
from v$lock v, dba_objects o
where 1=1
and TYPE in ('TX','TM')
and v.id1 = o.object_id(+)
order by sid, type;
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
397 TM OBJ$ 18 0 3 0 0 0 18
397 TM SYS_JOURNAL_70875 70876 0 6 0 0 1 5340
397 TM NPTABLE 70875 0 3 0 0 1 5339
397 TM SYS_RMTAB$$_H70875 70878 0 6 0 0 1 5342
397 TX 13434913 30182 6 0 0 205 33
397 TX 13434906 30169 6 0 0 205 26
1163 TM NPTABLE 70875 0 3 0 0 1 5339
1163 TX 13107204 29999 6 0 0 200 4
8 rows selected.
|
dml 세션들도 tm lock lmode 3으로 lock이 걸려서 서로 blocking 이 되지 않음
그림에 의하면 LMODE 3 (row-X (SX)) 과 LMODE 3 (row-X (SX)) 이 호환 된다고 나와있음
http://wiki.gurubee.net/display/STUDY/1.Lock
modify가 완료된 후 파티션 별 용량 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
SQL>
set lines 200 pages 1000
col owner for a10
col tablespace_name for a15
col segment_name for a15
col partition_name for a15
col segment_type for a15
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
--select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024/1024) gb
from dba_segments
where segment_name = 'NPTABLE'
and segment_type='TABLE PARTITION'
order by 3,4;
OWNER TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MB
---------- --------------- --------------- --------------- --------------- ----------
IMSI IMSITS NPTABLE P202101 TABLE PARTITION 10
IMSI IMSITS NPTABLE P202102 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202103 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202104 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202105 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202106 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202107 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202108 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202109 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202110 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202111 TABLE PARTITION 20
IMSI IMSITS NPTABLE P202112 TABLE PARTITION 10
12 rows selected.
|
정상적으로 파티션 테이블로 변경됨, 익스텐트 할당에 따라 용량이 약간 줄어듬
인덱스 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col table_name for a20
col partition_name for a20
select dip.index_owner, dpi.table_name, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='NPTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE TABLE_NAME INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT
---------- -------------------- -------------------- -------------------- -------- ------ ------------
IMSI NPTABLE NPTBL_IX01 P202101 USABLE GLOBAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202105 USABLE GLOBAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202112 USABLE GLOBAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P_MAX USABLE GLOBAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202101 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202102 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202103 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202104 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202105 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202106 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202107 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202108 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202109 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202110 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202111 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202112 USABLE LOCAL PREFIXED
16 rows selected.
|
인덱스까지 정상적으로 생성됨
통계정보 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'NPTABLE');
PL/SQL procedure successfully completed.
|
파티션별 row 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
col table_name for a10
col partition_name for a20
select table_name, partition_name, num_rows from dba_tab_partitions
where table_name = 'NPTABLE'
order by 1, 2;
TABLE_NAME PARTITION_NAME NUM_ROWS
---------- -------------------- ----------
NPTABLE P202101 454636
NPTABLE P202102 910773
NPTABLE P202103 909417
NPTABLE P202104 908636
NPTABLE P202105 908805
NPTABLE P202106 909456
NPTABLE P202107 910558
NPTABLE P202108 908277
NPTABLE P202109 909540
NPTABLE P202110 908028
NPTABLE P202111 907203
NPTABLE P202112 454671
12 rows selected.
|
row 수가 정확함
참고용1. alter table modify 명령시 online 옵션을 사용하지 않은 경우
alter 구문 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set timing on
alter table nptable
modify
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20220100')
);
Table altered.
|
modify 중 lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> 쿼리 생략
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK GUBUN USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ------- -------------------- ----------
1157 TM OBJ$ 18 0 3 0 0 WAITING 0 18
1157 TM NPTABLE 71205 0 6 0 0 WAITING 1 5669
1157 TM SYS_JOURNAL_71205 71212 0 6 0 0 WAITING 1 5676
1157 TM SYS_RMTAB$$_H71205 71214 0 6 0 0 WAITING 1 5678
1157 TX 13107207 30088 6 0 0 WAITING 200 7
1157 TX 13107213 30094 6 0 0 WAITING 200 13
6 rows selected.
|
NPTABLE 에 대해 tm lock lmode 6으로 잡혀서 dml이 불가함
modify 후 인덱스 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
SQL> 쿼리 생략
INDEX_OWNE TABLE_NAME INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT
---------- -------------------- -------------------- -------------------- -------- ------ ------------
IMSI NPTABLE NPTBL_IX01 P202101 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202102 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202103 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202104 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202105 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202106 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202107 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202108 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202109 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202110 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202111 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202112 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202101 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202102 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202103 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202104 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202105 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202106 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202107 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202108 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202109 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202110 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202111 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202112 USABLE LOCAL PREFIXED
24 rows selected.
|
기존에 global 인덱스가 존재했지만, 파티션 테이블로 변경된 이후 모든 인덱스가 local 인덱스로 변경됨
하지만 인덱스 상태는 모두 다행히 unusable이 아닌 사용 가능한 usable 상태임
global 인덱스를 원하는 경우 NPTBL_IX01 을 삭제하고 다시 만들어줘야함
참고용2. alter table modify online 명령시 update indexes 옵션을 사용하지 않은 경우
alter 구문 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set timing on
alter table nptable
modify
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20220100')
) online;
|
online 옵션을 사용했기 때문에 dml lock 은 발생하지 않음
인덱스 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
SQL> 쿼리 생략
INDEX_OWNE TABLE_NAME INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT
---------- -------------------- -------------------- -------------------- -------- ------ ------------
IMSI NPTABLE NPTBL_IX01 P202101 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202102 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202103 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202104 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202105 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202106 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202107 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202108 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202109 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202110 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202111 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX01 P202112 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202101 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202102 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202103 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202104 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202105 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202106 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202107 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202108 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202109 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202110 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202111 USABLE LOCAL PREFIXED
IMSI NPTABLE NPTBL_IX02 P202112 USABLE LOCAL PREFIXED
24 rows selected.
|
기존에 global 인덱스가 존재했지만, 파티션 테이블로 변경된 이후 모든 인덱스가 local 인덱스로 변경됨
하지만 인덱스 상태는 모두 다행히 unusable이 아닌 사용 가능한 usable 상태임
global 인덱스를 원하는 경우 NPTBL_IX01 을 삭제하고 다시 만들어줘야함
결론 :
12c부터 비파티션 테이블을 파티션 테이블로 바로 변경이 가능함
이때 변경하는 테이블에 대해 lmode 3 tm lock이 발생하여 온라인으로 변경이 가능한것임
online 옵션을 사용하지 않는 경우 변경하는 테이블에 대해 lmode 6 tm lock이 발생하여 작업 도중 dml이 불가능함
alter table 명령을 수행할 때 global 인덱스가 존재한다면 online update indexes 옵션을 사용해서 global 파티션 인덱스를 지정해줘야 정상적으로 생성됨
global 파티션 인덱스를 제대로 명시해주지 않고 online 옵션만 사용하는 경우 인덱스가 usable 상태로 생성이 되지만
local 인덱스만 정상적으로 생성되고, global 인덱스는 기존 global 파티션 형태가 아닌 local 파티션 인덱스로 생성이 됨
이 경우 global 인덱스를 원하는 경우 NPTBL_IX01 을 삭제하고 다시 만들어줘야함
online 옵션을 사용해서 작업중 dml 이 가능은 하지만, 운영환경에서는 트랜잭션이 적은 야간시간대에 수행하는걸 권장함
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/evolve-nopartition-table.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-9A31C4B1-1DA5-4DF8-8567-8642821056D6
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5
https://oracle-base.com/articles/12c/online-conversion-of-a-non-partitioned-table-to-a-partitioned-table-12cr2
https://positivemh.tistory.com/334
https://positivemh.tistory.com/882
https://positivemh.tistory.com/998
https://positivemh.tistory.com/1061
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c tar 명령을 이용해 db 엔진 복제 (0) | 2024.08.31 |
|---|---|
| 오라클 19c 골드 이미지 이용해 db 엔진 복제 (2) | 2024.08.27 |
| 오라클 19c sys 유저도 lock이 걸릴까? (0) | 2024.08.06 |
| 오라클 19c varchar2 컬럼에서 clob 으로 변경 가이드 (0) | 2024.07.31 |
| 오라클 12cR2 asm 환경 v$asm_disk 의 path 컬럼 값 (0) | 2024.07.17 |