0

The table is partitioned by an incrementing sequence number. Old partitions are mostly unused as a result, and I generally delete many of the rows in the old partitions, leading to large amounts of fragmentation. Most of my queries into this either return results on how to defragment a partition index, or points me to the defragmentation function in Oracle 20c. We can allow for the table to be locked during the weekends if that's necessary to defragment and reclaim the space.

Currently, the option that looks most viable to me is the "move partition" option, which from my understanding would drop the old partition segment and recreate it, which should defragment the table. Would this be a viable option?

1 Answer 1

0

That is the way to do it.

Starting with 12.1, partitions can be moved online.

Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1

move_table_partition

ONLINE

Specify ONLINE to indicate that DML operations on the table partition will be allowed while moving the table partition.

Restrictions on the ONLINE Clause

The ONLINE clause is subject to the following restrictions when moving table partitions:

You cannot specify the ONLINE clause for tables owned by SYS.

You cannot specify the ONLINE clause for index-organized tables.

You cannot specify the ONLINE clause for heap-organized tables that contain object types or on which bitmap join indexes or domain indexes are defined.

You cannot specify the ONLINE clause when database-level supplemental logging is enabled for the database. This restriction is removed starting with Oracle Database 12c Release 1 (12.1.0.2).

Parallel DML and direct path INSERT operations require an exclusive lock on the table. Therefore, these operations are not supported concurrently with an ongoing online partition MOVE, due to conflicting locks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.