13.3.13 ALTER TABLE… TRUNCATE PARTITION

Use the ALTER TABLE… TRUNCATE PARTITION command to remove the data from the specified partition, leaving the partition structure intact. The syntax is:

ALTER TABLE table_name TRUNCATE PARTITION partition_name
[{DROP|REUSE} STORAGE]

Description

Use the ALTER TABLE… TRUNCATE PARTITION command to remove the data from the specified partition, leaving the partition structure intact. When you truncate a partition, any subpartitions of that partition are also truncated.

ALTER TABLE… TRUNCATE PARTITION will not cause ON DELETE triggers that might exist for the table to fire, but it will fire ON TRUNCATE triggers. If an ON TRUNCATE trigger is defined for the partition, all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation occurs.

You must have the TRUNCATE privilege on a table to invoke ALTER TABLE… TRUNCATE PARTITION.

Parameters

table_name

The name (optionally schema-qualified) of the partitioned table.

partition_name

The name of the partition to be deleted.

DROP STORAGE and REUSE STORAGE are included for compatibility only; the clauses are parsed and ignored.

13.3.13.1 Example - Emptying a Partition

The example that follows removes the data from 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')

);

Populate the sales table with the command:

INSERT INTO sales VALUES

(10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),

(20, '3788a', 'INDIA', '01-Mar-2012', '75000'),

(40, '9519b', 'US', '12-Apr-2012', '145000'),

(20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),

(40, '4577b', 'US', '11-Nov-2012', '25000'),

(30, '7588b', 'CANADA', '14-Dec-2012', '50000'),

(30, '9519b', 'CANADA', '01-Feb-2012', '75000'),

(30, '4519b', 'CANADA', '08-Apr-2012', '120000'),

(40, '3788a', 'US', '12-May-2012', '4950'),

(10, '9519b', 'ITALY', '07-Jul-2012', '15000'),

(10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),

(10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),

(20, '3788b', 'INDIA', '21-Sept-2012', '5090'),

(40, '4788a', 'US', '23-Sept-2012', '4950'),

(40, '4788b', 'US', '09-Oct-2012', '15000'),

(20, '4519a', 'INDIA', '18-Oct-2012', '650000'),

(20, '4519b', 'INDIA', '2-Dec-2012', '5090');

Querying the sales table shows that the partitions are populated with data:

acctg=# SELECT tableoid::regclass, * FROM sales;

tableoid | dept_no | part_no | country | date | amount

----------------+---------+---------+----------+--------------------+--------

sales_europe | 10 | 4519b | FRANCE | 17-JAN-12 00:00:00 | 45000

sales_europe | 10 | 9519b | ITALY | 07-JUL-12 00:00:00 | 15000

sales_europe | 10 | 9519a | FRANCE | 18-AUG-12 00:00:00 | 650000

sales_europe | 10 | 9519b | FRANCE | 18-AUG-12 00:00:00 | 650000

sales_asia | 20 | 3788a | INDIA | 01-MAR-12 00:00:00 | 75000

sales_asia | 20 | 3788a | PAKISTAN | 04-JUN-12 00:00:00 | 37500

sales_asia | 20 | 3788b | INDIA | 21-SEP-12 00:00:00 | 5090

sales_asia | 20 | 4519a | INDIA | 18-OCT-12 00:00:00 | 650000

sales_asia | 20 | 4519b | INDIA | 02-DEC-12 00:00:00 | 5090

sales_americas | 40 | 9519b | US | 12-APR-12 00:00:00 | 145000

sales_americas | 40 | 4577b | US | 11-NOV-12 00:00:00 | 25000

sales_americas | 30 | 7588b | CANADA | 14-DEC-12 00:00:00 | 50000

sales_americas | 30 | 9519b | CANADA | 01-FEB-12 00:00:00 | 75000

sales_americas | 30 | 4519b | CANADA | 08-APR-12 00:00:00 | 120000

sales_americas | 40 | 3788a | US | 12-MAY-12 00:00:00 | 4950

sales_americas | 40 | 4788a | US | 23-SEP-12 00:00:00 | 4950

sales_americas | 40 | 4788b | US | 09-OCT-12 00:00:00 | 15000

(17 rows)

To delete the contents of the americas partition, invoke the following command:

ALTER TABLE sales TRUNCATE PARTITION americas;

Now, querying the sales table shows that the content of the americas partition has been removed:

acctg=# SELECT tableoid::regclass, * FROM sales;

tableoid | dept_no | part_no | country | date | amount

--------------+---------+---------+----------+--------------------+--------

sales_europe | 10 | 4519b | FRANCE | 17-JAN-12 00:00:00 | 45000

sales_europe | 10 | 9519b | ITALY | 07-JUL-12 00:00:00 | 15000

sales_europe | 10 | 9519a | FRANCE | 18-AUG-12 00:00:00 | 650000

sales_europe | 10 | 9519b | FRANCE | 18-AUG-12 00:00:00 | 650000

sales_asia | 20 | 3788a | INDIA | 01-MAR-12 00:00:00 | 75000

sales_asia | 20 | 3788a | PAKISTAN | 04-JUN-12 00:00:00 | 37500

sales_asia | 20 | 3788b | INDIA | 21-SEP-12 00:00:00 | 5090

sales_asia | 20 | 4519a | INDIA | 18-OCT-12 00:00:00 | 650000

sales_asia | 20 | 4519b | INDIA | 02-DEC-12 00:00:00 | 5090

(9 rows)

 

While the rows have been removed, the structure of the americas partition is still intact:

acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

partition_name | high_value

----------------+---------------------

europe | 'FRANCE', 'ITALY'

asia | 'INDIA', 'PAKISTAN'

americas | 'US', 'CANADA'

(3 rows)