Example - Deleting a Subpartition v11

Suggest edits

The example that follows deletes a subpartition of the sales table. Use the following command to 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')
);

Querying the ALL_TAB_SUBPARTITIONS view displays the subpartition names.

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

To delete the americas subpartition from the sales table, invoke the following command:

ALTER TABLE sales DROP SUBPARTITION americas;

Querying the ALL_TAB_SUBPARTITIONS view demonstrates that the subpartition has been successfully deleted.

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

ALTER TABLE...DROP SUBPARTITION

Next →

TRUNCATE TABLE