Truncate Partition Operation

Just like we do truncate operation on table , we can truncate partition too.

Here we will see 3 types of truncate operations.

1.Truncate Single Partition

2.Truncate Subpartition

3.Truncate Multiple Partitions together.

This post will made your DBA maintenance activity easier as all truncate operations we will see.

Let’s move to the first one topic.

1.Truncate Single Partition : 

As per the quarterly , half yearly or yearly maintenance activity , a DBA has to truncate some partitions from the table .

To make it very simple we will go step by step.

Step 1 : Let us check records of one of partitioned table TRANSACTIONS.

SQL>  SELECT COUNT(*) FROM TRANSACTIONS;

  COUNT(*)
----------
     20000

Step 2 : Now let us look at it’s all partitions

SQL> SELECT TABLE_NAME,COMPOSITE,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME                     COM PARTITION_NAME
------------------------------ --- ------------------------------
TRANSACTIONS                   NO  Y_2011
TRANSACTIONS                   NO  Y_2013
TRANSACTIONS                   NO  Y_2014
TRANSACTIONS                   NO  Y_2015
TRANSACTIONS                   NO  Y_2016
TRANSACTIONS                   NO  Y_2017
TRANSACTIONS                   NO  Y_2018
TRANSACTIONS                   NO  Y_2019
TRANSACTIONS                   NO  Y_2020

9 rows selected.

Step 3 : Let us look at records of Y_2013 individual partition :

SQL>  SELECT COUNT(*) FROM TRANSACTIONS PARTITION(Y_2013);

  COUNT(*)
----------
      4445

SQL>

Step 4 : Lets perform truncate subpartition operation :

SQL> ALTER TABLE TRANSACTIONS TRUNCATE PARTITION(Y_2013);

Table truncated.

Step 5 : Lets check partition Y_2013’s records :

SQL>  SELECT COUNT(*) FROM TRANSACTIONS PARTITION(Y_2013);

  COUNT(*)
----------
         0

In above output we can see all records are deleted for that specific partition. Lets look at whole table’s records :

SQL>  SELECT COUNT(*) FROM TRANSACTIONS;

  COUNT(*)
----------
     15555

We can see the whole table is not truncated and other partition’s records remained intact.

Let’s move to the second point of truncating subpartition now.

Just like we saw truncating partition we can truncate subpartition too.

2.Truncate Subpartition :

SQL> ALTER TABLE SUBSCRIPTION_DTL TRUNCATE SUBPARTITION P_RR1_R1;

Table truncated.

SQL>

You just need to give proper subpartition name and you can truncate it.

3.Truncate Multiple Partition :

Truncating single-single partition when you have hundreds of partition is tedious task.

But here the beauty is we can truncate multiple partition together.

Lets look at that operation.

Step 1 : With union all operator I checked count of two partitions – Y_2014 and Y_2015.

SQL>  SELECT COUNT(*) FROM TRANSACTIONS PARTITION(Y_2014) UNION ALL  SELECT COUNT(*) FROM TRANSACTIONS PARTITION(Y_2015);

  COUNT(*)
----------
      2222
      2222

Step 2 : Let’s truncate these two partitions together.

SQL> ALTER TABLE TRANSACTIONS TRUNCATE PARTITIONS Y_2014,Y_2015;

Table truncated.

SQL> 

As per above query we can give comma separated list of partitions you want to truncate.

Step 3 : Lets check those partitions are actually truncated or not

SQL> SELECT COUNT(*) FROM TRANSACTIONS PARTITION(Y_2014) UNION ALL  SELECT COUNT(*) FROM TRANSACTIONS PARTITION(Y_2015);

  COUNT(*)
----------
	 0
	 0

SQL> 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp