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.

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

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

Step 4 : Lets perform truncate subpartition operation :

Step 5 : Lets check partition Y_2013’s records :

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

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 :

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.

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

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

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

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.