Oracle Interval Partitioning After Update

Dear Experts,

I have a table A with interval partitioning. The partitioning key is XCURDATE with DATE type. I wonder what happens to the affected rows if I update XCURDATE to a future date using Update DML statement? Are afftected rows moved to a new partition? What happens to the previous partition, does this affect table size with redundant used space?

Best Regards.
Who is Participating?
sdstuberConnect With a Mentor Commented:
If you do have row movement enabled, then new partitions will be created as needed.
The space already allocated from the old partitions is NOT released when the row is moved out of the old partition into a new one.

Similar to deleting a row from a table, the space is still there, waiting to be used by another row.
slightwv (䄆 Netminder)Connect With a Mentor Commented:
It depends on how you created the table.

I find the online documentation to be an invaluable resource:

When you create (or alter) a partitioned table, a row movement clause (either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT) can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is DISABLE ROW MOVEMENT.
GurcanKAuthor Commented:
What can I do to get rid of that space?
Abhimanyu SuriSr Database EngineerCommented:
Shrink partitions is one way to get space back.
If all rows have been updated then partition can be truncated depending on application requirement.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.