If a partition is full and redistributing the data across
partitions is not practical, you might need to increase the partition
size.
About this task
You can increase the maximum partition size of a partitioned
table space to 128 GB or 256 GB. Depending on the partition size and
page size, increasing the maximum size of a partition can proportionally
reduce the maximum number of partitions that can be specified.
Procedure
To increase the maximum partition size of a partitioned
table space:
- If the table space uses index-based partitioning, convert
it to table-based partitioning by issuing the DROP statement to drop
the partitioning index.
- If the table space is not a universal table space, convert
it to one by issuing the ALTER TABLESPACE statement with the SEGSIZE
option. The value of the SEGSIZE option must not be zero.
- Issue the ALTER TABLESPACE statement with the DSSIZE option
to increase the maximum partition size to 128 GB or 256 GB.
- 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.
- 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.