Drop Interval Partition Encountered ORA-14758Last partition in the range section cannot be dropped－DanBrother的部落格

Drop Interval Partition Encountered ORA-14758 Last partition in the range section cannot be dropped

-- Create a Interval Partition table
create table TEST_TAB
(
ORDER_NO NUMBER(10),
ORDER_TIME DATE
)
partition by range (ORDER_TIME)
interval(NUMTOYMINTERVAL(1,'MONTH'))
store in (TBS_L01,TBS_L02,TBS_L03,TBS_L04,TBS_L05,TBS_L06,TBS_L07,TBS_L08,TBS_L09,TBS_L10,TBS_L11,TBS_L12)
(
partition TEST_TAB_20111231 values less than (TO_DATE('2012-01-01', 'YYYY-MM-DD'))
tablespace TBS_L01,
partition TEST_TAB_20120131 values less than (TO_DATE('2012-02-01', 'YYYY-MM-DD'))
tablespace TBS_L02
);

-- Insert some testing samples
SQL> insert into TEST_TAB(order_no,order_time)
values(100,to_date('2011-10-01','yyyy-mm-dd'));

SQL> insert into TEST_TAB(order_no,order_time)
values(200,to_date('2011-11-01','yyyy-mm-dd'));

SQL> insert into TEST_TAB(order_no,order_time)
values(300,to_date('2012-01-01','yyyy-mm-dd'));

SQL> col table_name format a10
SQL> col partition_name format a20
SQL> col high_value format a50
SQL> select table_name,partition_name,interval,high_value from user_tab_partitions
where table_name = 'TEST_TAB';

TABLE_NAME	PARTITION_NAME	INTERVAL	HIGH_VALUE
TEST_TAB	TEST_TAB_20111231	NO	TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24: MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_TAB	TEST_TAB_20120131	NO	TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24: MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select * from TEST_TAB;

ORDER_NO	ORDER_TIME
100	2011-10-01 00:00:00
200	2011-11-01 00:00:00
300	2012-01-01 00:00:00

insert into TEST_TAB(order_no,order_time)
values(400,to_date('2012-02-01','yyyy-mm-dd'));
COMMIT;

select * from TEST_TAB;
SQL> select * from TEST_TAB;

ORDER_NO	ORDER_TIME
100	2011-10-01 00:00:00
200	2011-11-01 00:00:00
300	2012-01-01 00:00:00
400	2012-02-01 00:00:00

select table_name,partition_name,interval,high_value from user_tab_partitions
where table_name = 'TEST_TAB';

TABLE_NAME	PARTITION_NAME	INTERVAL	HIGH_VALUE
TEST_TAB	SYS_P422	YES	TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_TAB	TEST_TAB_20111231	NO	TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_TAB	TEST_TAB_20120131	NO	TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> alter table TEST_TAB drop partition TEST_TAB_20111231;

Table altered.

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

Solution:
-- revert back to a range-partitioned table:
alter table TEST_TAB set interval ();

select table_name,partition_name,interval,high_value from user_tab_partitions
where table_name ='TEST_TAB';

TABLE_NAME	PARTITION_NAME	INTERVAL	HIGH_VALUE
TEST_TAB	SYS_P422	NO	TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_TAB	TEST_TAB_20120131	NO	TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

-- Now the partition can be dropped:
SQL> alter table TEST_TAB drop partition TEST_TAB_20120131;

Table altered.

-- Remember to set back the range partition to original interval partition:
SQL>alter table TEST_TAB set interval (NUMTOYMINTERVAL(1,'MONTH'));

Table altered.

-- The original round-bin tablespaces should also be set in the table:
SQL>alter table TEST_TAB set store in (TBS_L01,TBS_L02,TBS_L03,TBS_L04,TBS_L05,TBS_L06,TBS_L07,TBS_L08,TBS_L09,TBS_L10,TBS_L11,TBS_L12);

Table altered.

SQL>select table_name,partition_name,interval,high_value from user_tab_partitions
where table_name ='TEST_TAB';

TABLE_NAME	PARTITION_NAME	INTERVAL	HIGH_VALUE
TEST_TAB	SYS_P422	NO	TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL>select * from TEST_TAB;

ORDER_NO	ORDER_TIME
400	2012-02-01 00:00:00

insert into TEST_TAB(order_no,order_time)
values(500,to_date('2012-03-01','yyyy-mm-dd'));
COMMIT;

SQL>select * from TEST_TAB;

ORDER_NO	ORDER_TIME
400	2012-02-01 00:00:00
500	2012-03-01 00:00:00

SQL>select table_name,partition_name,interval,high_value from user_tab_partitions
where table_name ='TEST_TAB';

TABLE_NAME	PARTITION_NAME	INTERVAL	HIGH_VALUE
TEST_TAB	SYS_P422	NO	TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_TAB	SYS_P423	YES	TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

col segment_name format a20
col tablespace_name format a20
select segment_name,partition_name,tablespace_name
from user_segments
where segment_name = 'TEST_TAB';

SEGMENT_NAME	PARTITION_NAME	TABLESPACE_NAME
TEST_TAB	SYS_P422	TBS_L03
TEST_TAB	SYS_P423	TBS_L02

col table_name format a10
col interval format a26
select table_name,partitioning_type,status,interval
from user_part_tables
where table_name = 'TEST_TAB';