Find objects that will fail to extend

Related to checking the amount of free space in a tablespace, you may want to check for objects that are in imminent danger of failing to extend. To do this, you need to check for three things:

1. Objects with next extent values greater than the largest free extent in that object'stablespace
2. Tablespaces with minimum extent sizes greater than their largest free extent
3. Objects that have already been allocated their maximum number of extents

You can check for these cases by querying Oracle's data dictionary views.

You should consider adding space to any tablespaces identified by these queries to ensure that you have enough free space to allow objects that are stored in that tablespace to grow in size. You can add space by adding data files or by increasing the size of existing data files. If you find objects that have reached their maximum in terms of the number of extents, consider increasing that maximum.

If you use the autoextend option when creating the data files for a tablespace, Oracle will attempt to resize those files whenever more free space is needed. Consequently, segments in that tablespace may be able to extend even if there is currently not enough free space for them to do so. If you're using Oracle8i, be aware that Oracle8i implements a new type of tablespace known as the locally managed tablespace. There are different rules for extent allocation in locally managed tablespaces, and the queries shown here don't apply.

Click the Quiz link to answer a few questions about free space and extents.
Checking for Objects - Quiz