今天发现oracle11g中用oracle自动创建分区的新功能后，删除旧分区报ORA-14758: Last partition in the range section cannot be dropped错误。

oracle对此错误的解释是：

实验如下：

1.创建自动分区的表：

SQL> create table TB_DYNA_TABLE
(
  NUM                        NUMBER,
  NAME                       VARCHAR2(30),
  CRT_TIME                   DATE not null
)
partition by range (CRT_TIME)
interval(numtodsinterval(1,'DAY'))
(PARTITION P20120919 VALUES LESS THAN (TO_DATE(' 2012-09-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
根据创建时间按天创建分区表。

插入几条数据：

SQL>
SQL> INSERT INTO TB_DYNA_TABLE(NUM,NAME,CRT_TIME)
  2  VALUES(1,'DONGCS1',TO_DATE('2012-09-15','YYYY-MM-DD'));
 
1 row inserted
SQL> INSERT INTO TB_DYNA_TABLE(NUM,NAME,CRT_TIME)
  2  VALUES(2,'DONGCS2',TO_DATE('2012-09-20','YYYY-MM-DD'));
 
1 row inserted
SQL> INSERT INTO TB_DYNA_TABLE(NUM,NAME,CRT_TIME)
  2  VALUES(3,'DONGCS3',TO_DATE('2012-09-21','YYYY-MM-DD'));
 
1 row inserted
SQL> INSERT INTO TB_DYNA_TABLE(NUM,NAME,CRT_TIME)
  2  VALUES(4,'DONGCS4',TO_DATE('2012-09-22','YYYY-MM-DD'));
 
1 row inserted
SQL> INSERT INTO TB_DYNA_TABLE(NUM,NAME,CRT_TIME)
  2  VALUES(5,'DONGCS5',TO_DATE('2012-09-23','YYYY-MM-DD'));
 
1 row inserted
SQL> INSERT INTO TB_DYNA_TABLE(NUM,NAME,CRT_TIME)
  2  VALUES(6,'DONGCS6',TO_DATE('2012-09-24','YYYY-MM-DD'));
 
1 row inserted
 
SQL> commit;
 
Commit complete

查询数据情况：

SQL> select * from TB_DYNA_TABLE;
 
       NUM NAME                           CRT_TIME
---------- ------------------------------ -----------
         1 DONGCS1                        2012-9-15
         2 DONGCS2                        2012-9-20
         3 DONGCS3                        2012-9-21
         4 DONGCS4                        2012-9-22
         5 DONGCS5                        2012-9-23
         6 DONGCS6                        2012-9-24
 
6 rows selected

查询表的分区信息:

SQL> SELECT t.table_name,t.partition_name,t.partition_position FROM User_Tab_Partitions t WHERE t.table_name='TB_DYNA_TABLE';
 
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------------------ ------------------
TB_DYNA_TABLE                  P20120919                                       1
TB_DYNA_TABLE                  SYS_P61                                         2
TB_DYNA_TABLE                  SYS_P63                                         3
TB_DYNA_TABLE                  SYS_P62                                         4
TB_DYNA_TABLE                  SYS_P65                                         5
TB_DYNA_TABLE                  SYS_P64                                         6
 
6 rows selected

删除第一个分区看看：

SQL> alter table TB_DYNA_TABLE drop partition P20120919;
 
alter table TB_DYNA_TABLE drop partition P20120919
 
ORA-14758: Last partition in the range section cannot be dropped

看来第一个分区删不了。
 删除其他分区看看：

SQL>  alter table TB_DYNA_TABLE drop partition SYS_P61;
 
Table altered
 
SQL> alter table TB_DYNA_TABLE drop partition SYS_P63;
 
Table altered

其他分区可以删除。

查询表的分区信息：

SQL> SELECT t.table_name,t.partition_name,t.partition_position FROM User_Tab_Partitions t WHERE t.table_name='TB_DYNA_TABLE';
 
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------------------ ------------------
TB_DYNA_TABLE                  P20120919                                       1
TB_DYNA_TABLE                  SYS_P62                                         2
TB_DYNA_TABLE                  SYS_P65                                         3
TB_DYNA_TABLE                  SYS_P64                                         4
总结：

1.建表时需要指定第一个初始化的分区

2.插入数据后，分区根据插入的数据自动分区了；

3.删除第一个分区删除不了，只能删除之后的分区。

另外还需注意：如果表有索引且不是分区索引，删除分区后（分区里面且有数据），那么需要重建索引。