Thinking Out Loud

March 14, 2020

ORA-14758: Last partition in the range section cannot be dropped

Filed under: 12.2,partitioning — mdinh @ 7:27 pm

Quick and dirty post.

Works for 12.2.0.1 and failed with ORA-14758 below 12.2.0.1

00:51:45 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P661        YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

10 rows selected.

00:51:49 DINH @ HAWK:HAWK:>alter table test drop partition D1;

Table altered.

00:52:00 DINH @ HAWK:HAWK:>alter table test drop partition D2;

Table altered.

00:52:08 DINH @ HAWK:HAWK:>alter table test drop partition D3;

Table altered.

00:52:20 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

00:52:23 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P662;

Table altered.

00:52:46 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P663;

Table altered.

00:52:58 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P664;

Table altered.

00:53:09 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:53:12 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P665;

Table altered.

00:53:43 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P666;

Table altered.

00:53:53 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:04 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P667;

Table altered.

00:54:22 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:28 DINH @ HAWK:HAWK:>insert into test values(to_date('01.01.2009', 'dd.mm.yyyy'), 1);

1 row created.

00:54:36 DINH @ HAWK:HAWK:>commit;

Commit complete.

00:54:41 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P668        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:44 DINH @ HAWK:HAWK:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

====================================================================================================

18:59:19 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- -------------------------------------------------------------------------------------
MDINH        TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P68         YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P69         YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P70         YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P71         YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P72         YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P73         YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P74         YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

10 rows selected.

18:59:40 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D1;

Table altered.

18:59:50 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D2;

Table altered.

18:59:58 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


19:00:06 DB-FS-1:(MDINH@hawk):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@db-fs-1 sql]$

SOLUTION:

I have been discussing this with LDC and agree the better solution is to TRUNCATE the partition.

How To Avoid ORA-14758 in Interval Partitioned Table (Doc ID 1526571.1)
(iv) Temporarily disable the interval partition.

Here is one example of Temporarily disable the interval partition.
This has side effects as shown which has worked until one day it stopped working.
Test cases are simple and real world may not be as such.

12:44:18 DB-FS-1:(MDINH@hawk):PRIMARY> @p

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         D1              NO                             TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D2              NO                             TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D3              NO                             TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P101        YES                            TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        YES                            TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        YES                            TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        YES                            TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        YES                            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        YES                            TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        YES                            TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

10 rows selected.

12:44:20 DB-FS-1:(MDINH@hawk):PRIMARY> drop alter table test drop partition D1;
drop alter table test drop partition D1
     *
ERROR at line 1:
ORA-00950: invalid DROP option


12:44:48 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D1;

Table altered.

12:45:45 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D2;

Table altered.

12:45:57 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


12:46:07 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test truncate partition D3;

Table truncated.

12:46:31 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST set interval ();

Table altered.

12:50:23 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST drop partition D3;

Table altered.

12:50:44 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST set interval (numtoyminterval(1,'YEAR'));

Table altered.

12:50:53 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         SYS_P101        NO                             TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        NO                             TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        NO                             TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        NO                             TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        NO                             TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        NO                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        NO                             TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

7 rows selected.

12:50:58 DB-FS-1:(MDINH@hawk):PRIMARY> insert into test values(to_date('01.01.2010', 'dd.mm.yyyy'), 1);

1 row created.

12:52:15 DB-FS-1:(MDINH@hawk):PRIMARY> commit;

Commit complete.

12:54:36 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME           POS INT HIGH_VALUE
------------ ------------ --------------- ------------ --- ----------------------------------------------------------
MDINH        TEST         SYS_P101                   1 NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102                   2 NO  TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103                   3 NO  TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104                   4 NO  TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105                   5 NO  TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106                   6 NO  TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107                   7 NO  TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P108                   8 YES TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

8 rows selected.

12:54:40 DB-FS-1:(MDINH@hawk):PRIMARY>

February 17, 2016

Converting to Interval Paritions

Filed under: 11g,partitioning — mdinh @ 11:24 pm

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
$

Blog at WordPress.com.