Oracle Live SQL - Script: Truncating a Range Partitioned Table

Statement 1

Create a partitioned table

CREATE TABLE sales_partition_truncate 
  ( product_id       NUMBER(6) NOT NULL, 
    customer_id      NUMBER    NOT NULL, 
    channel_id       CHAR(1), 
    promo_id         NUMBER(6), 
    sales_date       DATE, 
    quantity_sold    INTEGER, 
    amount_sold      NUMBER(10,2) 
  ) 
 PARTITION BY RANGE (sales_date)  
 SUBPARTITION BY LIST (channel_id) 
(  PARTITION q3_2018 VALUES LESS THAN (TO_DATE('1-OCT-2018','DD-MON-YYYY')) 
   ( SUBPARTITION q3_2018_p_catalog VALUES ('C'), 
     SUBPARTITION q3_2018_p_internet VALUES ('I'), 
     SUBPARTITION q3_2018_p_partners VALUES ('P'), 
     SUBPARTITION q3_2018_p_direct_sales VALUES ('S'), 
     SUBPARTITION q3_2018_p_tele_sales VALUES ('T') 
   ), 
  PARTITION q4_2018 VALUES LESS THAN (TO_DATE('1-JAN-2019','DD-MON-YYYY')) 
   ( SUBPARTITION q4_2018_p_catalog VALUES ('C'), 
     SUBPARTITION q4_2018_p_internet VALUES ('I'), 
     SUBPARTITION q4_2018_p_partners VALUES ('P'), 
     SUBPARTITION q4_2018_p_direct_sales VALUES ('S'), 
     SUBPARTITION q4_2018_p_tele_sales VALUES ('T') 
   ), 
  PARTITION q1_2019 VALUES LESS THAN (TO_DATE('1-APR-2019','DD-MON-YYYY')) 
   ( SUBPARTITION q1_2019_p_catalog VALUES ('C') 
   , SUBPARTITION q1_2019_p_internet VALUES ('I') 
   , SUBPARTITION q1_2019_p_partners VALUES ('P') 
   , SUBPARTITION q1_2019_p_direct_sales VALUES ('S') 
   , SUBPARTITION q1_2019_p_tele_sales VALUES ('T') 
   ), 
  PARTITION q2_2019 VALUES LESS THAN (TO_DATE('1-JUL-2019','DD-MON-YYYY')) 
   ( SUBPARTITION q2_2019_p_catalog VALUES ('C'), 
     SUBPARTITION q2_2019_p_internet VALUES ('I'), 
     SUBPARTITION q2_2019_p_partners VALUES ('P'), 
     SUBPARTITION q2_2019_p_direct_sales VALUES ('S'), 
     SUBPARTITION q2_2019_p_tele_sales VALUES ('T') 
   ), 
  PARTITION q3_2019 VALUES LESS THAN (TO_DATE('1-OCT-2019','DD-MON-YYYY')) 
   ( SUBPARTITION q3_2019_p_catalog VALUES ('C'), 
     SUBPARTITION q3_2019_p_internet VALUES ('I'), 
     SUBPARTITION q3_2019_p_partners VALUES ('P'), 
     SUBPARTITION q3_2019_p_direct_sales VALUES ('S'), 
     SUBPARTITION q3_2019_p_tele_sales VALUES ('T') 
   ), 
  PARTITION q4_2019 VALUES LESS THAN (TO_DATE('1-JAN-2020','DD-MON-YYYY')) 
   ( SUBPARTITION q4_2019_p_catalog VALUES ('C'), 
     SUBPARTITION q4_2019_p_internet VALUES ('I'), 
     SUBPARTITION q4_2019_p_partners VALUES ('P'), 
     SUBPARTITION q4_2019_p_direct_sales VALUES ('S'), 
     SUBPARTITION q4_2019_p_tele_sales VALUES ('T') 
   ) 
  )

Table created.

Statement 2

Insert data into some of the partitions

INSERT INTO sales_partition_truncate VALUES (1001,100,'C',150,'10-SEP-2018',500,2000)

1 row(s) inserted.

Statement 3

INSERT INTO sales_partition_truncate VALUES (1021,200,'C',160,'16-NOV-2018',100,1500)

1 row(s) inserted.

Statement 4

INSERT INTO sales_partition_truncate VALUES (1001,100,'C',150,'10-FEB-2019',500,2000)

1 row(s) inserted.

Statement 5

INSERT INTO sales_partition_truncate VALUES (1021,200,'S',160,'16-FEB-2019',100,1500)

1 row(s) inserted.

Statement 6

INSERT INTO sales_partition_truncate VALUES (1002,110,'I',180,'15-JUN-2019',100,1000)

1 row(s) inserted.

Statement 7

INSERT INTO sales_partition_truncate VALUES (5010,150,'P',200,'20-AUG-2019',1000,10000)

1 row(s) inserted.

Statement 8

INSERT INTO sales_partition_truncate VALUES (1001,100,'T',150,'12-OCT-2019',500,2000)

1 row(s) inserted.

Statement 9

Display all the data that is currently in the table

SELECT * FROM sales_partition_truncate


PRODUCT_ID CUSTOMER_ID CHANNEL_ID PROMO_ID SALES_DATE QUANTITY_SOLD AMOUNT_SOLD
1001 100 C 150 10-SEP-18 500 2000
1021 200 C 160 16-NOV-18 100 1500
1001 100 C 150 10-FEB-19 500 2000
1021 200 S 160 16-FEB-19 100 1500
1002 110 I 180 15-JUN-19 100 1000
5010 150 P 200 20-AUG-19 1000 10000
1001 100 T 150 12-OCT-19 500 2000

7 rows selected.

PRODUCT_ID	CUSTOMER_ID	CHANNEL_ID	PROMO_ID	SALES_DATE	QUANTITY_SOLD	AMOUNT_SOLD
1001	100	C	150	10-SEP-18	500	2000
1021	200	C	160	16-NOV-18	100	1500
1001	100	C	150	10-FEB-19	500	2000
1021	200	S	160	16-FEB-19	100	1500
1002	110	I	180	15-JUN-19	100	1000
5010	150	P	200	20-AUG-19	1000	10000
1001	100	T	150	12-OCT-19	500	2000

Statement 10

Display all the partitions and subpartitions in the table

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='SALES_PARTITION_TRUNCATE'


TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_CATALOG
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_INTERNET
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_CATALOG
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_INTERNET
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_TELE_SALES

30 rows selected.

TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME
SALES_PARTITION_TRUNCATE	Q1_2019	Q1_2019_P_CATALOG
SALES_PARTITION_TRUNCATE	Q1_2019	Q1_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE	Q1_2019	Q1_2019_P_INTERNET
SALES_PARTITION_TRUNCATE	Q1_2019	Q1_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE	Q1_2019	Q1_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE	Q2_2019	Q2_2019_P_CATALOG
SALES_PARTITION_TRUNCATE	Q2_2019	Q2_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE	Q2_2019	Q2_2019_P_INTERNET
SALES_PARTITION_TRUNCATE	Q2_2019	Q2_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE	Q2_2019	Q2_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE	Q3_2018	Q3_2018_P_CATALOG
SALES_PARTITION_TRUNCATE	Q3_2018	Q3_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE	Q3_2018	Q3_2018_P_INTERNET
SALES_PARTITION_TRUNCATE	Q3_2018	Q3_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE	Q3_2018	Q3_2018_P_TELE_SALES
SALES_PARTITION_TRUNCATE	Q3_2019	Q3_2019_P_CATALOG
SALES_PARTITION_TRUNCATE	Q3_2019	Q3_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE	Q3_2019	Q3_2019_P_INTERNET
SALES_PARTITION_TRUNCATE	Q3_2019	Q3_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE	Q3_2019	Q3_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE	Q4_2018	Q4_2018_P_CATALOG
SALES_PARTITION_TRUNCATE	Q4_2018	Q4_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE	Q4_2018	Q4_2018_P_INTERNET
SALES_PARTITION_TRUNCATE	Q4_2018	Q4_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE	Q4_2018	Q4_2018_P_TELE_SALES
SALES_PARTITION_TRUNCATE	Q4_2019	Q4_2019_P_CATALOG
SALES_PARTITION_TRUNCATE	Q4_2019	Q4_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE	Q4_2019	Q4_2019_P_INTERNET
SALES_PARTITION_TRUNCATE	Q4_2019	Q4_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE	Q4_2019	Q4_2019_P_TELE_SALES

Statement 11

Truncate multiple partitions, and the associated subpartitions

ALTER TABLE sales_partition_truncate 
   TRUNCATE PARTITIONS q3_2018, q4_2018

Table altered.

Statement 12

Display all the data that is currently in the table partitions, noting that data from multiple partitions has been removed

SELECT * FROM sales_partition_truncate


PRODUCT_ID CUSTOMER_ID CHANNEL_ID PROMO_ID SALES_DATE QUANTITY_SOLD AMOUNT_SOLD
1001 100 C 150 10-FEB-19 500 2000
1021 200 S 160 16-FEB-19 100 1500
1002 110 I 180 15-JUN-19 100 1000
5010 150 P 200 20-AUG-19 1000 10000
1001 100 T 150 12-OCT-19 500 2000

5 rows selected.

PRODUCT_ID	CUSTOMER_ID	CHANNEL_ID	PROMO_ID	SALES_DATE	QUANTITY_SOLD	AMOUNT_SOLD
1001	100	C	150	10-FEB-19	500	2000
1021	200	S	160	16-FEB-19	100	1500
1002	110	I	180	15-JUN-19	100	1000
5010	150	P	200	20-AUG-19	1000	10000
1001	100	T	150	12-OCT-19	500	2000

Statement 13

Display all the partitions and subpartitions in the table, noting that the truncating operation removes data and not partitions or subpartitions

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='SALES_PARTITION_TRUNCATE'


TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q1_2019 Q1_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q2_2019 Q2_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_CATALOG
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_INTERNET
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE Q3_2018 Q3_2018_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q3_2019 Q3_2019_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_CATALOG
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_INTERNET
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE Q4_2018 Q4_2018_P_TELE_SALES
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_CATALOG
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_INTERNET
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_PARTNERS
SALES_PARTITION_TRUNCATE Q4_2019 Q4_2019_P_TELE_SALES

30 rows selected.

Truncating a Range Partitioned Table

Additional Information