Database Monitoring   «Prev  Next»
Lesson 9Checking for objects that will not extend
Objective Find objects that will fail to extend.

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.

Finding objects that will fail to extend

The following slide shows some data dictionary queries that you can use to identify objects that will not be able to extend:
1) The buyer (consumer) buys the digital equivalent of money from
2) A digitized check is encrypted (we will discuss security in a later
3) A consumer enters credit card information on a Web order form

What to do?

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.

In the next lesson, you will compute the cache hit ratio.

Checking for Objects - Quiz

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