[split] maxvalue가 있는 range 파티션에 다음 파티션 추가하기

월별로 파티션이 생성되는 테이블에서 그다음 파티션을 추가하려면 add partition 명령어를 사용합니다.
하지만 maxvalue가 설정되어 있는 경우도 자주 볼 수 있는데요, 이경우에 add partition을 하면 ora 에러가 발생합니다.

 

ORA-14074: partition bound must collate higher than that of the last partition

마지막 파티션이 존재하므로 무조건 그거보다 높게 설정해야 한다는 뜻입니다.
하지만 마지막 파티션이 maxvalue인 경우에는 그거보다 높은 값이 없습니다. 
그래서 maxvalue를 split하여 값도 나누고 원하는 월 파티션을 추가하는 방법이 있습니다.

 

1.테이블 생성

CREATE TABLE PARTITION_TEST
(
    CLSG_YM                 VARCHAR2(6) NOT NULL,
    IS_PLNO                 VARCHAR2(12) NOT NULL
)
NOCOMPRESS
PARTITION BY RANGE (CLSG_YM)
(
    PARTITION PT_PARTITION_TEST_201612 VALUES LESS THAN ('201701')
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOCOMPRESS,
    PARTITION PT_PARTITION_TEST_201701 VALUES LESS THAN ('201702')

    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOCOMPRESS,
    PARTITION PT_PARTITION_TEST_MAXVALUE VALUES LESS THAN (MAXVALUE)
    STORAGE
    (
        INITIAL 4M
        NEXT 4M
    )
    NOCOMPRESS
);

 

2.데이터 삽입

각 파티션에 5개씩 들어가게 데이터 삽입.

INSERT INTO PARTITION_TEST VALUES ('201612',1);
INSERT INTO PARTITION_TEST VALUES ('201612',2);
INSERT INTO PARTITION_TEST VALUES ('201612',3);
INSERT INTO PARTITION_TEST VALUES ('201612',4);
INSERT INTO PARTITION_TEST VALUES ('201612',5);

INSERT INTO PARTITION_TEST VALUES ('201701',6);
INSERT INTO PARTITION_TEST VALUES ('201701',7);
INSERT INTO PARTITION_TEST VALUES ('201701',8);
INSERT INTO PARTITION_TEST VALUES ('201701',9);
INSERT INTO PARTITION_TEST VALUES ('201701',10);

INSERT INTO PARTITION_TEST VALUES ('201702',11);
INSERT INTO PARTITION_TEST VALUES ('201702',12);
INSERT INTO PARTITION_TEST VALUES ('201702',13);
INSERT INTO PARTITION_TEST VALUES ('201702',14);
INSERT INTO PARTITION_TEST VALUES ('201702',15);

commit;

 

3.확인

select count(*) from PARTITION_TEST
union all
select count(*) from PARTITION_TEST partition (PT_PARTITION_TEST_201612)
union all
select count(*) from PARTITION_TEST partition (PT_PARTITION_TEST_201701)
union all
select count(*) from PARTITION_TEST partition (PT_PARTITION_TEST_maxvalue)

COUNT(*)         
---------------- 
              15
               5
               5
               5

4 rows selected.


테이블 파티션 확인

SQL> select table_name,partition_name,high_Value From dba_tab_partitions
where table_name='PARTITION_TEST';

TABLE_NAME     PARTITION_NAME                HIGH_VALUE            
-------------------------------------------- --------------------- 
PARTITION_TEST PT_PARTITION_TEST_201612                   '201701'
PARTITION_TEST PT_PARTITION_TEST_201701                   '201702'
PARTITION_TEST PT_PARTITION_TEST_MAXVALUE                 MAXVALUE

3 rows selected.

 

 

4.파티션 add

에러 발생

SQL> alter table PARTITION_TEST add partition PT_PARTITION_TEST_201702 values less than ('201703');

ORA-14074: partition bound must collate higher than that of the last partition

 

5.파티션 split

maxvalue 를 '201703' 값 기준으로 2개로 split합니다.

SQL> alter table PARTITION_TEST split partition PT_PARTITION_TEST_MAXVALUE at ('201703')
into (partition PT_PARTITION_TEST_201702,
      partition PT_PARTITION_TEST_MAXVALUE);

Statement Processed.

 

6.확인

SQL> select count(*) from PARTITION_TEST
union all
select count(*) from PARTITION_TEST partition (PT_PARTITION_TEST_201612)
union all
select count(*) from PARTITION_TEST partition (PT_PARTITION_TEST_201701)
union all
select count(*) from PARTITION_TEST partition (PT_PARTITION_TEST_201702)
union all
select count(*) from PARTITION_TEST partition (PT_PARTITION_TEST_maxvalue);

COUNT(*)         
---------------- 
              15
               5
               5
               5
               0

5 rows selected.
select table_name,partition_name,high_Value From dba_tab_partitions
where table_name='PARTITION_TEST'

TABLE_NAME     PARTITION_NAME                HIGH_VALUE            
-------------------------------------------- --------------------- 
PARTITION_TEST PT_PARTITION_TEST_201612                   '201701'
PARTITION_TEST PT_PARTITION_TEST_201701                   '201702'
PARTITION_TEST PT_PARTITION_TEST_201702	                  '201703'
PARTITION_TEST PT_PARTITION_TEST_MAXVALUE                 MAXVALUE