As we saw in previous posts by just defining number of partitions we can create partitions in HASH partition or *-hash subpartitions paritioning type.
Now to remove some partition we can use coalesce partition feature of oracle.

After we apply coalescing function one hash partition is removed and data are redistributed among the partitions which are suitable with hashing function of oracle.
In this post we will see coalescing partition and coalescing sub partition from one of *-hash composite partition.

1.Coalescing Partition :
Step 1 : Create table with 7 partitions.

Check number of partitions in USER_PART_TABLES table.

We can check here there are 7 partitions available. Lets coalesce partition with COALESCE PARTITION command.

Now let us check number of partitions available in USER_PART_TABLES table.

Let us again repeat the same practice.

We can see in above resultset after we apply coalesce partition command each one hash partition is removed from the list.

Coalesce subpartition :

Same like coalescing Partitions , when we perform coalesce subpartition operation , one subpartition reduces and the rows of that subpartition is distributed among remaining subpartitions as per hashing function.

Step 1 : Here I checked with group by query in user_tab_subpartitions table.

We can see in above resultset every partition consists of 4 subpartitions each.

Now let’s perform coalescing subpartition operation .

Step 2 : Coalesce Subpartition :

Step 3 : Let’s check count of subpartitions now.

We can see in above resultset Y_2018 partition has 3 subpartitions now which was 4 before.

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:

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.