Just a quick note to demonstrate how to convert date range partition into interval partition.
Create test case:
ARROW:(MDINH@hawklas):PRIMARY> @test_part.sql
ARROW:(MDINH@hawklas):PRIMARY> set echo on
ARROW:(MDINH@hawklas):PRIMARY> drop table t_part purge;
Table dropped.
ARROW:(MDINH@hawklas):PRIMARY> create table t_part (
2 col_date DATE
3 )
4 PARTITION BY RANGE (col_date)
5 (
6 PARTITION P2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
7 PARTITION P2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
8 PARTITION PMAX VALUES LESS THAN (MAXVALUE)
9 );
Table created.
ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2014-01-01','YYYY-MM-DD'));
1 row created.
ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2015-01-01','YYYY-MM-DD'));
1 row created.
ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2016-01-01','YYYY-MM-DD'));
1 row created.
ARROW:(MDINH@hawklas):PRIMARY> commit;
Commit complete.
ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);
PL/SQL procedure successfully completed.
Check partitions:
ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;
TABLE_OWNER TABLE_NAME INT PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH T_PART NO P2014 1 0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO P2015 1 0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO PMAX 1 0 MAXVALUE
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 3 0 1 USERS
Failed due to PMAX partition as shown above:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.
Split partition to remove PMAX:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part split partition PMAX AT(TO_DATE('2017-01-01', 'YYYY-MM-DD')) into (partition P2017, partition PMAX);
Table altered.
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part drop partition pmax;
Table altered.
Check partition:
ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;
TABLE_OWNER TABLE_NAME INT PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH T_PART NO P2014 1 0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO P2015 1 0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO P2017 1 0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 3 0 1 USERS
Wrong syntax:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
*
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type
Correct syntax:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(NUMTOYMINTERVAL(1,'year'));
Table altered.
Check partition:
ARROW:(MDINH@hawklas):PRIMARY> @part
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;
TABLE_OWNER TABLE_NAME INT PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH T_PART NO P2014 1 0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO P2015 1 0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO P2017 1 0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 1048575 0 1 USERS
Add data:
ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2018-01-01','YYYY-MM-DD'));
1 row created.
ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2019-01-01','YYYY-MM-DD'));
1 row created.
ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);
PL/SQL procedure successfully completed.
Check partition:
ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;
TABLE_OWNER TABLE_NAME INT PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH T_PART NO P2014 1 0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO P2015 1 0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART NO P2017 1 0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART YES SYS_P541 1 0 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH T_PART YES SYS_P542 1 0 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 1048575 0 1 USERS
ARROW:(MDINH@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawklas:/media/sf_working/sql
$
