꿈꾸는 개발자, DBA 커뮤니티 구루비

여러 파티션을 한꺼번에 Altering 가능하다

12c 에서 partition 2개의 partition 을 add, 1개의 파티션을 3개로 split 할 경우 아래와 같이 가능하다.


SQL> alter table orders add
partition p_4 values less than (to_Date('2018/01/01','yyyy/mm/dd')),
partition p_5 values less than (to_Date('2019/01/01','yyyy/mm/dd'));

Statement Processed.


SQL> alter table orders split partition p_1 into (
partition p_1_1 values less than (to_date('2013/01/01','yyyy/mm/dd')),
partition p_1_2 values less than (to_date('2014/01/01','yyyy/mm/dd')),
partition p_1_3 );

Statement Processed.


SQL>  select
table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS');  2    3

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDERS                         P_1_1                          TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_1_2                          TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_1_3                          TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         55
ORDERS                         P_4                            TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_5                            TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

반면 11g 에서는 아래와 같은 에러가 발생한다.


SQL> alter table orders add
partition p_4 values less than (to_Date('2018/01/01','yyyy/mm/dd')),
partition p_5 values less than (to_Date('2019/01/01','yyyy/mm/dd'))
ORA-14043: only one partition may be added


SQL> alter table orders split partition p_1 into (
partition p_1_1 values less than (to_date('2013/01/01','yyyy/mm/dd')),
partition p_1_2 values less than (to_date('2014/01/01','yyyy/mm/dd')),
partition p_1_3 )
ORA-14028: missing AT or VALUES keyword

11g 에서 동일한 결과를 얻으려면 아래와 같이 개별 파티션으로 작업 해야 한다


SQL> alter table orders add
partition p_4 values less than (to_Date('2018/01/01','yyyy/mm/dd'));

Statement Processed.

SQL> alter table orders add
partition p_5 values less than (to_Date('2019/01/01','yyyy/mm/dd'));

Statement Processed.


SQL> alter table orders split partition p_1 at (to_date('2013/01/01','yyyy/mm/dd')) into (
partition p_1_1,
partition p_1_2
);

Statement Processed.

SQL> alter table orders split partition p_1_2 at (to_date('2014/01/01','yyyy/mm/dd')) into (
partition p_1_2,
partition p_1_3
);

Statement Processed.


SQL>  select
table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS');

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDERS                         P_1_1                          TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      66187
ORDERS                         P_1_2                          TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_1_3                          TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         12
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      20208
ORDERS                         P_4                            TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_5                            TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

SQL>

추가로 Partition merge 도 가능 해 졌다

SQL> alter table orders merge partitions p_1_1, p_1_2, p_1_3 into partition p_1;

Statement Processed.


SQL> /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDERS                         P_5                            TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_4                            TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         55
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         P_1                            TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL>