Example - Deleting a Partition v11
The example that follows deletes a partition 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 LIST(country) ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') );
Querying the ALL_TAB_PARTITIONS
view displays the partition names.
edb=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS; partition_name | high_value ----------------+------------------------------------- EUROPE | FOR VALUES IN ('FRANCE', 'ITALY') ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN') AMERICAS | FOR VALUES IN ('US', 'CANADA') (3 rows)
To delete the americas partition from the sales
table, invoke the following command:
ALTER TABLE sales DROP PARTITION americas;
Querying the ALL_TAB_PARTITIONS
view demonstrates that the partition has been successfully deleted.
edb=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS; partition_name | high_value ----------------+------------------------------------- EUROPE | FOR VALUES IN ('FRANCE', 'ITALY') ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN') (2 rows)