Monitor metadata for a interval partition with a rolling window

We may hit below error when dropping a partition using interval range.

SQL> alter table orders2 drop partition p_second;
alter table orders2 drop partition p_second
                                   *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


As mentioned in "How to Use Interval Partitioning with a Rolling Partition Window and Avoid ORA-14758 (Doc ID 1590833.1)",

When you create an interval partitioned table, you must create at least one partition and specify the interval. This specified partition is created as a range partition; all future partitions that are automatically created by the database are interval
partitions. A known limitation is that you cannot drop the last range partition of an interval partitioned table. That means you cannot drop the first (last) partition that you specified when you created the table.
1. Enhancement Bug 17571086 : ENH: ALLOW DROP OF OLDEST PARTITION IN ROLLING WINDOW WHEN INTERVAL PARTITIONED is implemented in 12.2 and resolves this issue. This is a 12.2 feature and cannot be backported to lower
RDBMS versions.
2. Until 12.2 is released and your database is upgraded to 12.2, here is the description of and workaround to the issue:



Below experiment list down metadata changing over the maintenance.

1.  create the test table

create table orders2
(
invoice_no number not null,
invoice_date date not null,
comments varchar2(100)
)
partition by range (invoice_date)
interval (numtoyminterval(1,'month'))
(partition p_first values less than (to_date('01-Jan-2012','DD-MON-YYYY')) ,
 partition p_second values less than (to_date('01-Feb-2012','DD-MON-YYYY')) );

2. insert two test rows BUT not to commit

 Insert into orders2 values (150,'03-JAN-2012','test');

-- this follows into partition p_second

 Insert into orders2 values (150,'03-Feb-2012','test');
-- this created a new parttion

3. query the partitions

SQL>   select partition_name, partition_position, high_value from dba_tab_partitions where table_name='ORDERS2' order by partition_position;

PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
P_FIRST                                         1
TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

P_SECOND                                        2
TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P194                                        3
TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P194 is the last partition.

3. the current metadata
  CREATE TABLE "LIQY"."ORDERS2"
   ( "INVOICE_NO" NUMBER NOT NULL ENABLE,
"INVOICE_DATE" DATE NOT NULL ENABLE,
"COMMENTS" VARCHAR2(100 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("INVOICE_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
 (PARTITION "P_FIRST"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "P_SECOND"  VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;


However, in the metadata, the P_SECOND is still shown as last partition, while SYS_P194 not updated to the metadata.    P_SECOND is so called statically partition

4.   try to drop the STATICALLY last partition P_SECOND


alter table orders2 drop partition p_second;

SQL> alter table orders2 drop partition p_second;
alter table orders2 drop partition p_second
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


--commit the INSERT session now and try again



SQL> alter table orders2 drop partition p_second;
alter table orders2 drop partition p_second
                                   *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


5. use the workaround for DB prior to 12.2

5.1   reset it to normal range partition table and drop the partition

SQL> alter table orders2 set interval ();

Table altered.

SQL>  alter table orders2 drop partition p_second;

Table altered.


5.2 monitor the metadata again. Notice the metadata gets updated.


 CREATE TABLE "LIQY"."ORDERS2"
   ( "INVOICE_NO" NUMBER NOT NULL ENABLE,
"INVOICE_DATE" DATE NOT NULL ENABLE,
"COMMENTS" VARCHAR2(100 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("INVOICE_DATE") 
 (PARTITION "P_FIRST"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "SYS_P194"  VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;



SQL>   select partition_name, partition_position, high_value from dba_tab_partitions where table_name='ORDERS2' order by partition_position;

PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
P_FIRST                                         1
TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P194                                        2
TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA



6.1 change the partition back to interval
SQL>  alter table orders2 set interval (numtoyminterval(1,'month'));

Table altered.


6.2 monitor the metadata again

 CREATE TABLE "LIQY"."ORDERS2"
   ( "INVOICE_NO" NUMBER NOT NULL ENABLE,
"INVOICE_DATE" DATE NOT NULL ENABLE,
"COMMENTS" VARCHAR2(100 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("INVOICE_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
 (PARTITION "P_FIRST"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "SYS_P194"  VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) ;