Increasing partition size

If a partition is full and redistributing the data across partitions is not practical, you might need to increase the partition size.

To increase the maximum partition size of a partitioned table space:

1. If the table space uses index-based partitioning, convert it to table-based partitioning, as described in Converting table spaces to use table-controlled partitioning.
2. If the table space is not a partition-by-range universal table space, convert it as described in Converting partitioned (non-UTS) table spaces to partition-by-range universal table spaces.
3. Issue the ALTER TABLESPACE statement with the DSSIZE option to increase the maximum partition size to 128 GB or 256 GB.
   For table spaces that use relative page numbering, you can also specify the DSSIZE value at the partition level.
4. Issue the ALTER TABLESPACE statement with the PRIQTY and SECQTY options to modify the primary and secondary space allocation for each partition.
   This change allows the partition to grow to its anticipated maximum size.
5. Run the REORG TABLESPACE utility with SHRLEVEL CHANGE or SHRLEVEL REFERENCE to materialize the pending definition changes and convert the table space.
   When reorganizing a table space that has pending definition changes, the entire table space must be included. Therefore, you cannot reorganize by partition. In addition, partition parallelism is disabled during the UNLOAD and RELOAD phases.

   A significant amount of disk space can be required when reorganizing an entire table space. The amount of space required for the table space and indexes is approximately two times of what is already allocated. If the amount of space that is required is not available, you might need to use an alternative strategy of unloading, dropping, creating, and loading the table space. With this method, you can reorganize individual partitions in parallel and requires significantly less disk space.

   For tables that have LOB and XML columns, the table spaces are independent from the base table space. You can alter and reorganize these table spaces separately. Do not run the REORG utility with AUX YES to reorganize both the base and LOB table spaces together, because in this case, the pending definition changes for the LOB table space are not materialized.

   Converting a 16 TB table space to a table with larger partitions or data sets can take a significant amount of time.