Example: Deleting a subpartition v17

Suggest edits

This example deletes a subpartition of the sales table.

Create the sales table:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
  SUBPARTITION BY LIST (country)
  (
    PARTITION first_half_2012 VALUES LESS THAN('01-JUL-2012')
    (
      SUBPARTITION europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION americas VALUES ('CANADA', 'US'),
      SUBPARTITION asia VALUES ('PAKISTAN', 'INDIA')
    ),
    PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013')
);

Query the ALL_TAB_SUBPARTITIONS view to display the subpartition names:

edb=# SELECT subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
Output
 subpartition_name |     high_value
-------------------+---------------------
 EUROPE            | 'ITALY', 'FRANCE'
 AMERICAS          | 'CANADA', 'US'
 ASIA              | 'PAKISTAN', 'INDIA'
 SYS0101           | DEFAULT
(4 rows)

Delete the americas subpartition from the sales table:

ALTER TABLE sales DROP SUBPARTITION americas;

Query the ALL_TAB_SUBPARTITIONS view to show that the subpartition was successfully deleted:

edb=# SELECT subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
Output
 subpartition_name |     high_value
-------------------+---------------------
 EUROPE            | 'ITALY', 'FRANCE'
 ASIA              | 'PAKISTAN', 'INDIA'
 SYS0101           | DEFAULT
(3 rows)
← Prev

ALTER TABLE...DROP SUBPARTITION

↑ Up

ALTER TABLE...DROP SUBPARTITION

Next →

TRUNCATE TABLE