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

Q.  You cannot drop the only partition in a table. Instead, you must drop the table.
- Published on 28 Jul 15

a. True
b. False

ANSWER: True
 

    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


Enter the code shown above:
 
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)