9 SpaceHow do I…
9.1 Detect objects close to maximum extents?
9.2 Detect row chaining and migration in tables?
9.3 Correct row migration in tables?
9.4 Determine and coalesce the free space in a tablespace?
9.5 Allocate unlimited extents for a database object?
9.6 Deallocate unused space in a segment?
9.7 Configure database files to extend automatically?
Space management is one of the primary responsibilities for database administrators. Recent improvements in the Oracle server have simplified this task so that database space management does not consume so much of a DBA’s time. These enhancements include unlimited extents, the capability to deallocate space in a segment, and the capability to coalesce fragmented space in a tablespace. The series of How-To’s in this chapter explain how to use these features of the Oracle server to simplify space management.
9.1 Detect Objects Close to Maximum Extents
Application and user activity contribute to the growth of database tables and indexes. These objects can grow out of their first storage allocation or extent and, in fact, they can grow into multiple extents. Although the UNLIMITED extent option is available in Oracle8, DBAs can choose to limit the number of maximum extents for any object so that it cannot grow without restriction. This How-To explains detection of objects close to this physical limit on their growth so that the DBA can take action before the situation becomes problematic.
9.2 Detect Row Chaining and Migration in Tables
Oracle8 databases, or any databases really, do not perform at their best unless database tuners take steps to minimize physical input/output. The Oracle8 server stores data in blocks, where the size of a block is chosen at database creation time.
Sometimes parts of a single table row inhabit more than one block. If a modification increases a row’s size beyond the capacity of its original data block, Oracle8 moves the row to another block and maintains a pointer to the row’s new location in its original data block. This is called row migration.
Row chaining, in contrast, occurs when a table row is too large to fit in any database block. In either case, this “one row, many blocks” situation forces Oracle8 to perform extra physical I/O to access to chained or migrated rows. This How-to presents a method for detecting table rows that require more than one data block.
9.3 Correct Row Migration in Tables
The How-To 9.2 addressed detection of chained and migrated rows. Most of the time, Oracle users and DBAs cannot resolve row chaining. Row migration, however, is often reversible. This How-To outlines a method to ease row migration difficulties in Oracle8 databases.
9.4 Determine and Coalesce the Free Space in a Tablespace
When tablespaces run low on free space, the Oracle8 server can generate a plethora of error messages, including “unable to allocate initial extent” and “failed to extend.” The Oracle8 data dictionary can help DBAs and privileged users identify which tablespaces are approaching conditions like these.
This How-To outlines a method to identify these kinds of tablespaces and also contains an explanation of the difference between free space and contiguous free space. Finally, this How-To describes how to use the ALTER_TABLESPACE command to coalesce fragmented free space in a tablespace.
9.5 Allocate Unlimited Extents for a Database Object
Oracle objects are composed of extents, and, until recently, the number of extents that the Oracle server could allocate to any object was limited. The addition of unlimited extents capability can considerably reduce space management efforts because it enables Oracle to dynamically allocate as many extents to an object as necessary.