Dropping Table partition or Index partition is a part of routine maintenance job of DBA.
When to drop or add partition ?
Well, it depends on the requirement . For instance , in range partitioned tables whenever new range comes in the picture we need to add new partition for the table if we have not created maxvalue partition. On the other hand , if you have list partitioned table with company at different zones or location and those locations are shutting off , you need to drop those partitions if no longer needed. Or some old ranged data if not needed we can simply drop that particular partition.
Things to be considered while dropping partition.
1.We can drop partition of only range and list partitioned table. Other partitioning type does not allow drop partition.
2.When you apply drop partition command , one partition must remain.You can not drop last partition , drop table instead.
3.Drop partition is DDL statement.Hence, no rollback can be performed after it. It deletes rows instantly.
4.You can not drop partition from reference-partitioned table . It should be done on parent table and it will be cascaded to all child tables.
5.You should be owner of the table or have DROP ANY TABLE privilege to drop partition.
6.You can drop partition from the table which just have single partition. Drop the table instead.
Dropping Index Partitions :
1.You cannot drop local index directly. When you drop table partition associated local indexes will be dropped.
2.You can drop partition from Global index partitions and after you drop the global index partition all the entries of that partition will be re-created in next higher value partition.
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