내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
에러 : ORA-14074: partition bound must collate higher than that of the last partition
oracle 19c 에서 파티션 테이블에 add 파티션을 하려고 할때 발생한 문제
|
1
2
3
4
5
|
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
|
해결 방법 : max파티션 drop 후 add하거나 파티션 split
기존 파티션 구성 확인
|
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 파티션이 존재함
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 파티션이 존재하기 때문에 에러가 발생
maxvalue 파티션을 지우고 add 하거나 split을 해줘야 에러가 발생하지 않음
참조 : https://positivemh.tistory.com/839
오라클 19c 파티션 테이블 생성, 파티션 add, split
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 파티션 테이블 생성, 파티션 add, split 오라클 19c에서 파티션 테이블을 생성하고 maxvalue 파티션이 없는 경..
positivemh.tistory.com