You cannot drop the only partition in a table - Oracle Indexes and Partitioned Tables

Discussion

• Nirja Shah   -Posted on 29 Sep 15
  - You can drop partitions from range, interval, list, or composite *-[range | list] partitioned tables.
  
  - For interval partitioned tables, you can only drop range or interval partitions that have been materialized.
  
  - For hash-partitioned tables, or hash subpartitions of composite *-hash partitioned tables, you must perform a coalesce operation instead.
  
  - You cannot drop a partition from a reference-partitioned table. Instead, a drop operation on a parent table cascades to all descendant tables.
  
  Dropping Table Partitions
  
  - Use one of the following statements to drop a table partition or subpartition:
  
  1. ALTER TABLE ... DROP PARTITION to drop a table partition
  2. ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table
  
  - To preserve the data in the partition, then use the MERGE PARTITION statement instead of the DROP PARTITION statement.
  
  - If local indexes are defined for the table, then this statement also drops the matching partition or subpartitions from the local index.
  
  - All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE unless either of the following is true:
  
  1. You specify UPDATE INDEXES (Cannot be specified for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)
  2. The partition being dropped or its subpartitions are empty

➨ Post your comment / Share knowledge