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>

2 Comments »

  1. What is the solution for it. This is what I exactly got while testing for a script that I was writing to automate the monthly drop partition

    Comment by Kuldip Singh — March 20, 2020 @ 6:39 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

%d bloggers like this: