Reorganization of partition-by-growth table spaces

When you run the REORG TABLESPACE utility on a partition-by-growth table space, the utility condenses the data into the fewest partitions possible. If necessary, the utility adds partitions, if possible.

If you reorganize a partition-by-growth table space that does not contain LOB columns, REORG TABLESPACE condenses the data into the minimum number of required partitions. Because the utility cannot reclaim physical space, the excess partitions are empty. If the data needs additional space and the maximum number of partitions has not been reached, REORG TABLESPACE adds additional partitions. If the maximum number of partitions has been reached, the utility fails.

If the partition-by-growth table space contains LOB columns, REORG TABLESPACE minimizes partitions by eliminating existing holes, but does not move the data from one partition to another.

When you reorganize a partition-by-growth table space at the partition level, REORG TABLESPACE minimizes partitions by eliminating existing holes. If you specify REORG TABLESPACE PART for a partition-by-growth table space and the data does not fit back into its partition, the utility fails. This situation can happen for one of the following reasons:

Start of change
  • A partition does not have enough space to insert the data and system page for the table.

    To prevent the utility from failing, run REORG TABLESPACE on the entire table space.

  • The amount of free space changes during the REORG.

    To prevent the utility from failing, run REORG TABLESPACE on the entire table space, or modify the PCTFREE parameter to fit the data rows into the partition.

End of change

In the following situations, the addition of new partitions might lead to the failure of REORG TABLESPACE because of a lack of disk space:

  • When REORG TABLESPACE is run against a subset of the partitions in a partition-by-growth table space
  • When REORG TABLESPACE AUX NO is run against a partition-by-growth table space in which a table contains LOB columns

For these situations only, you can alleviate space problems by taking one of the following actions:

  • Execute ALTER TABLESPACE on the table space to change PCTFREE and FREEPAGE to 0. This action is the preferred solution.
  • Instead of running REORG TABLESPACE against a subset of the partitions in a partition-by-growth table space, run REORG on the entire table space.
  • Set the REORG_IGNORE_FREESPACE subsystem parameter to YES. Doing so causes REORG TABLESPACE to not honor the PCTFREE and FREEPAGE values that are defined for the table space. REORG uses 0 for the PCTFREE and FREEPAGE values when it reloads data into the table space.

REORG parallelism does not apply to partition-by-growth table spaces. This restriction ensures that REORG TABLESPACE is able to condense the data into the minimum number of required partitions,

If a compression dictionary exists, the compression dictionary is copied to all partitions even if the partition is empty.