프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c 파티션 테이블 생성, 파티션 add, split

오라클 19c에서 파티션 테이블을 생성하고 maxvalue 파티션이 없는 경우 파티션을 추가하고 

maxvalue 파티션이 있는경우 split 하는 방법을 설명함

 

 

샘플 테이블 생성(maxvalue 미존재)

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>
drop table ptable1 purge;
create table ptable1
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('202102'),
partition p202102 values less than ('202103'),
partition p202103 values less than ('202104'),
partition p202104 values less than ('202105'),
partition p202105 values less than ('202106'),
partition p202106 values less than ('202107'),
partition p202107 values less than ('202108'),
partition p202108 values less than ('202109'),
partition p202109 values less than ('202110'),
partition p202110 values less than ('202111'),
partition p202111 values less than ('202112'),
partition p202112 values less than ('202113')
--partition p_max values less than (maxvalue)
)
;

 

 

샘플 데이터 삽입

1
2
3
4
5
6
7
8
SQL>
insert into ptable1 values ('202101',1);
insert into ptable1 values ('202102',1);
insert into ptable1 values ('202102',1);
insert into ptable1 values ('202103',1);
insert into ptable1 values ('202104',1);
insert into ptable1 values ('202105',1);
commit;

 

 

파티션 확인

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 lines 200 pages 1000
col table_owner for a8
col table_name for a8
col partition_name for a8
col high_value for a10
col tablespace_name for a10
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE1'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE1  P202101  '202102'   USERS        8388608             NO
IMSI     PTABLE1  P202102  '202103'   USERS        8388608             NO
IMSI     PTABLE1  P202103  '202104'   USERS        8388608             NO
IMSI     PTABLE1  P202104  '202105'   USERS        8388608             NO
IMSI     PTABLE1  P202105  '202106'   USERS        8388608             NO
IMSI     PTABLE1  P202106  '202107'   USERS                        NO
IMSI     PTABLE1  P202107  '202108'   USERS                        NO
IMSI     PTABLE1  P202108  '202109'   USERS                        NO
IMSI     PTABLE1  P202109  '202110'   USERS                        NO
IMSI     PTABLE1  P202110  '202111'   USERS                        NO
IMSI     PTABLE1  P202111  '202112'   USERS                        NO
IMSI     PTABLE1  P202112  '202113'   USERS                        NO
 
12 rows selected.

 

 

추가 파티션 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>
alter table ptable1 add partition p202201 values less than ('202202');
alter table ptable1 add partition p202202 values less than ('202203');
alter table ptable1 add partition p202203 values less than ('202204');
alter table ptable1 add partition p202204 values less than ('202205');
alter table ptable1 add partition p202205 values less than ('202206');
alter table ptable1 add partition p202206 values less than ('202207');
alter table ptable1 add partition p202207 values less than ('202208');
alter table ptable1 add partition p202208 values less than ('202209');
alter table ptable1 add partition p202209 values less than ('202210');
alter table ptable1 add partition p202210 values less than ('202211');
alter table ptable1 add partition p202211 values less than ('202212');
alter table ptable1 add partition p202212 values less than ('202213');

 

 

파티션 재확인

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
35
36
37
38
39
40
41
42
43
44
45
46
SQL>
set lines 200 pages 1000
col table_owner for a8
col table_name for a8
col partition_name for a8
col high_value for a10
col tablespace_name for a10
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE1'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE1  P202101  '202102'   USERS        8388608             NO
IMSI     PTABLE1  P202102  '202103'   USERS        8388608             NO
IMSI     PTABLE1  P202103  '202104'   USERS        8388608             NO
IMSI     PTABLE1  P202104  '202105'   USERS        8388608             NO
IMSI     PTABLE1  P202105  '202106'   USERS        8388608             NO
IMSI     PTABLE1  P202106  '202107'   USERS                        NO
IMSI     PTABLE1  P202107  '202108'   USERS                        NO
IMSI     PTABLE1  P202108  '202109'   USERS                        NO
IMSI     PTABLE1  P202109  '202110'   USERS                        NO
IMSI     PTABLE1  P202110  '202111'   USERS                        NO
IMSI     PTABLE1  P202111  '202112'   USERS                        NO
IMSI     PTABLE1  P202112  '202113'   USERS                        NO
IMSI     PTABLE1  P202201  '202202'   USERS                        NO
IMSI     PTABLE1  P202202  '202203'   USERS                        NO
IMSI     PTABLE1  P202203  '202204'   USERS                        NO
IMSI     PTABLE1  P202204  '202205'   USERS                        NO
IMSI     PTABLE1  P202205  '202206'   USERS                        NO
IMSI     PTABLE1  P202206  '202207'   USERS                        NO
IMSI     PTABLE1  P202207  '202208'   USERS                        NO
IMSI     PTABLE1  P202208  '202209'   USERS                        NO
IMSI     PTABLE1  P202209  '202210'   USERS                        NO
IMSI     PTABLE1  P202210  '202211'   USERS                        NO
IMSI     PTABLE1  P202211  '202212'   USERS                        NO
IMSI     PTABLE1  P202212  '202213'   USERS                        NO
 
24 rows selected.

 

 

결론 : maxvalue 파티션이 없는 상태에서는 파티션 add가 가능하기 때문에 add만 해주면됨

 

 

샘플 테이블 생성(maxvalue 존재)

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>
drop table ptable2 purge;
create table ptable2
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('202102'),
partition p202102 values less than ('202103'),
partition p202103 values less than ('202104'),
partition p202104 values less than ('202105'),
partition p202105 values less than ('202106'),
partition p202106 values less than ('202107'),
partition p202107 values less than ('202108'),
partition p202108 values less than ('202109'),
partition p202109 values less than ('202110'),
partition p202110 values less than ('202111'),
partition p202111 values less than ('202112'),
partition p202112 values less than ('202113'),
partition p_max values less than (maxvalue)
)
;

 

 

샘플 데이터 삽입

1
2
3
4
5
6
7
8
SQL>
insert into PTABLE2 values ('202101',1);
insert into PTABLE2 values ('202102',1);
insert into PTABLE2 values ('202102',1);
insert into PTABLE2 values ('202103',1);
insert into PTABLE2 values ('202104',1);
insert into PTABLE2 values ('202105',1);
commit;

 

 

파티션 확인

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
35
SQL>
set lines 200 pages 1000
col table_owner for a8
col table_name for a8
col partition_name for a8
col high_value for a10
col tablespace_name for a10
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE2'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE2  P202101  '202102'   USERS        8388608             NO
IMSI     PTABLE2  P202102  '202103'   USERS        8388608             NO
IMSI     PTABLE2  P202103  '202104'   USERS        8388608             NO
IMSI     PTABLE2  P202104  '202105'   USERS        8388608             NO
IMSI     PTABLE2  P202105  '202106'   USERS        8388608             NO
IMSI     PTABLE2  P202106  '202107'   USERS                        NO
IMSI     PTABLE2  P202107  '202108'   USERS                        NO
IMSI     PTABLE2  P202108  '202109'   USERS                        NO
IMSI     PTABLE2  P202109  '202110'   USERS                        NO
IMSI     PTABLE2  P202110  '202111'   USERS                        NO
IMSI     PTABLE2  P202111  '202112'   USERS                        NO
IMSI     PTABLE2  P202112  '202113'   USERS                        NO
IMSI     PTABLE2  P_MAX    MAXVALUE   USERS                        NO
 
13 rows selected.

MAXVALUE가 존재함

 

 

파티션 add 시도

1
2
3
4
5
6
SQL>
SQL> alter table PTABLE2 add partition p202201 values less than ('202202');
alter table PTABLE2 add partition p202201 values less than ('202202')
                                  *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

에러가 발생함, MAXVALUE가 존재하는 경우 MAXVALUE 파티션을 drop 후 신규 파티션을 add 하거나 split 해야함

 

 

MAXVALUE 파티션 drop 후 add 테스트

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
35
SQL>
alter table PTABLE2 drop partition P_MAX;
alter table PTABLE2 add partition p202201 values less than ('202202');
alter table PTABLE2 add partition p_max values less than (maxvalue);
 
조회
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE2'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE2  P202101  '202102'   USERS        8388608             NO
IMSI     PTABLE2  P202102  '202103'   USERS        8388608             NO
IMSI     PTABLE2  P202103  '202104'   USERS        8388608             NO
IMSI     PTABLE2  P202104  '202105'   USERS        8388608             NO
IMSI     PTABLE2  P202105  '202106'   USERS        8388608             NO
IMSI     PTABLE2  P202106  '202107'   USERS                        NO
IMSI     PTABLE2  P202107  '202108'   USERS                        NO
IMSI     PTABLE2  P202108  '202109'   USERS                        NO
IMSI     PTABLE2  P202109  '202110'   USERS                        NO
IMSI     PTABLE2  P202110  '202111'   USERS                        NO
IMSI     PTABLE2  P202111  '202112'   USERS                        NO
IMSI     PTABLE2  P202112  '202113'   USERS                        NO
IMSI     PTABLE2  P202201  '202202'   USERS                        NO  <------ 추가됨
IMSI     PTABLE2  P_MAX    MAXVALUE   USERS                        NO
 
14 rows selected.

정상적으로 추가됨

 

 

파티션 split(방금 추가한 p202201 삭제 후 진행)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>
alter table PTABLE2 drop partition p202201 ;
alter table PTABLE2 split partition p_max at ('202202'into (partition p202201, partition p_max);
alter table PTABLE2 split partition p_max at ('202203'into (partition p202202, partition p_max);
alter table PTABLE2 split partition p_max at ('202204'into (partition p202203, partition p_max);
alter table PTABLE2 split partition p_max at ('202205'into (partition p202204, partition p_max);
alter table PTABLE2 split partition p_max at ('202206'into (partition p202205, partition p_max);
alter table PTABLE2 split partition p_max at ('202207'into (partition p202206, partition p_max);
alter table PTABLE2 split partition p_max at ('202208'into (partition p202207, partition p_max);
alter table PTABLE2 split partition p_max at ('202209'into (partition p202208, partition p_max);
alter table PTABLE2 split partition p_max at ('202210'into (partition p202209, partition p_max);
alter table PTABLE2 split partition p_max at ('202211'into (partition p202210, partition p_max);
alter table PTABLE2 split partition p_max at ('202212'into (partition p202211, partition p_max);
alter table PTABLE2 split partition p_max at ('202213'into (partition p202212, partition p_max);

 

 

파티션 재확인

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
35
36
37
38
39
40
41
42
43
44
45
46
47
SQL>
set lines 200 pages 1000
col table_owner for a8
col table_name for a8
col partition_name for a8
col high_value for a10
col tablespace_name for a10
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE2'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE2  P202101  '202102'   USERS        8388608             NO
IMSI     PTABLE2  P202102  '202103'   USERS        8388608             NO
IMSI     PTABLE2  P202103  '202104'   USERS        8388608             NO
IMSI     PTABLE2  P202104  '202105'   USERS        8388608             NO
IMSI     PTABLE2  P202105  '202106'   USERS        8388608             NO
IMSI     PTABLE2  P202106  '202107'   USERS                        NO
IMSI     PTABLE2  P202107  '202108'   USERS                        NO
IMSI     PTABLE2  P202108  '202109'   USERS                        NO
IMSI     PTABLE2  P202109  '202110'   USERS                        NO
IMSI     PTABLE2  P202110  '202111'   USERS                        NO
IMSI     PTABLE2  P202111  '202112'   USERS                        NO
IMSI     PTABLE2  P202112  '202113'   USERS                        NO
IMSI     PTABLE2  P202201  '202202'   USERS                        NO
IMSI     PTABLE2  P202202  '202203'   USERS                        NO
IMSI     PTABLE2  P202203  '202204'   USERS                        NO
IMSI     PTABLE2  P202204  '202205'   USERS                        NO
IMSI     PTABLE2  P202205  '202206'   USERS                        NO
IMSI     PTABLE2  P202206  '202207'   USERS                        NO
IMSI     PTABLE2  P202207  '202208'   USERS                        NO
IMSI     PTABLE2  P202208  '202209'   USERS                        NO
IMSI     PTABLE2  P202209  '202210'   USERS                        NO
IMSI     PTABLE2  P202210  '202211'   USERS                        NO
IMSI     PTABLE2  P202211  '202212'   USERS                        NO
IMSI     PTABLE2  P202212  '202213'   USERS                        NO
IMSI     PTABLE2  P_MAX    MAXVALUE   USERS                        NO
 
25 rows selected.

정상적으로 추가 파티션이 생성됨(split 됨)

 

 

결론 : maxvalue 값이 없는 경우는 add 를 해주면되고 maxvalue값이 있는경우 split 해주면됨

(maxvalue 파티션을 drop 한뒤 add 하는것도 가능함)

 

 

참조 :

100299.1, 727306.1

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2098.htm#i1591118

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/partition-create-tables-indexes.html#GUID-66D19C36-C512-48DE-88B4-AFC6CCACDFEB