Tuesday, December 20, 2016

Will interrupting a "split partition"-command cause any harm to your database?

Will interrupting a "alter table split partition"-command cause any harm to your database?

No, it will not. It is safe to kill the session. Oracle will be able to recover and the table would be left in its original state, prior to time when the "alter table split partition" command was executed.

I was recently in contact with Oracle support regarding such a case. My goal was to split a sub-partition, in order to accommodate rows for years 2017 to 2020, but there was way too much concurrent activities in the database to complete the "alter table split partition"-command.
The database was almost completely non-responsive and there was pressure to finish within a certain time frame.

The supporting engineer confirmed that there would be some rollback activities in the wake of this command, how much depended on the activity level in the database during the time when the DDL was executing.

He added the following explanation to how Oracle handles the split internally:

As for kill the split partition, the way it works internally is to create temporary segments for the 2 new partitions it is splitting into, and when the operation is complete, these new temporary segments are attached to the original table and become the new partitions. So the result of this is that it's perfectly safe to terminate the split partition, and it simply stops its work, drops the temporary segments, and you are left with the original table in-tact, so there will be no ill-effects of this.

No comments:

Post a Comment