9.1 How do I…Detect objects close to maximum extents?Problem
I have created some database objects and have either accepted the default for the number of maximum extents or imposed a maximum extent limit of my own. I want to query my database periodically to determine whether any of these objects are close to a point where the maximum extent limit will not permit them to grow. How can I determine which tables and indexes are close to their limit for maximum extents?
Technique
The Oracle8 server allocates storage space in extents. The data definition language (DDL) commands that create tables and indexes can contain a storage clause specifying the size of the first extent, the next extent, and the amount by which to increase extents subsequent to the second extent. There is controversy regarding the advisability of confining database objects to a single extent. One philosophy holds that this practice minimizes input/output processing. Another school of thought maintains that the alleged I/O performance improvements don’t justify the effort of confining objects to a single extent. In any case, there is no doubt that maximally extended objects are likely to cause problems. The Oracle8 data dictionary view DBA_SEGMENTS contains the informa-tion you need to discover whether any database objects are close to the MAXEXTENTS limit. Table 9.1 contains a description of this data dictionary object.
Column Column Description OWNER User name of the segment owner SEGMENT_NAME Name, if any, of the segment SEGMENT_TYPE Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, or CACHE TABLESPACE_NAME Name of the tablespace containing the q segment HEADER_FILE ID of the file containing the segment header HEADER_BLOCK ID of the block containing the segment header BYTES Size, in bytes, of the segment BLOCKS Size, in Oracle blocks, of the segment EXTENTS Number of extents allocated to the segment INITIAL_EXTENT Size, in bytes, of the initial extent of the segment NEXT_EXTENT Size, in bytes, of the next extent to be allocated to the segment MIN_EXTENTS Minimum number of extents allowed in the segment MAX_EXTENTS Maximum number of extents allowed in the segment PCT_INCREASE Percent by which to increase the size of the next extent to be allocated FREELISTS Number of process freelists allocated to the segment FREELIST_GROUPS Number of freelist groups allocated to the segment RELATIVE_FNO Relative file number of the segment header PARTITION_NAME Object partition name (Set to NULL for non-partitioned objects) Steps
1. Run SQL*Plus, connect as the WAITE user, and use the SQL*Plus START command to run the script contained in CHP9_1.SQL. This script creates a partitioned table (see How-To 4.10 for an in-depth explanation of object partitioning in Oracle8) and is shown in Figure 9.1.
This example is contrived; it is unlikely that any developer would specify 3 for the maximum number of extents for the first partition of this table. Here, it will serve to illustrate the next step.
2. While still connected to SQL*Plus as the WAITE user, use the SQL*Plus START command to run the script contained in CHP9_2.SQL. The script will prompt for the substitution variable EXTLIMIT and will produce a report listing any database objects that are within EXTLIMIT extents of their maximum extent limit. The script and its output appear in Fig-ure 9.2.
This script queries the DBA_SEGMENTS data dictionary view. Experienced Oracle users will likely have familiarity with a script like this one that they developed for use with previous versions of Oracle. The one main difference is the inclusion of the PARTITION_NAME column that supports Oracle8 object partitioning.
How It Works
The DBA_SEGMENTS view contains everything you need to produce a meaningful report of all database objects that are close to maximum extension. Notice in Figure 9.2, for example, that a table and an index that user SCOTT owns, a CACHE object that user SYS owns, and the first partition of the EMP09 table created in Step 1 are all within five extents of their maximum extent limits.
Comments
Beginning with Oracle server release 7.3, DBAs can specify that objects grow through an unlimited number of extents. Tables and indexes featuring this option can grow until they consume all the available space in the tablespace. The script in step 2 will not fail, even in the presence of the UNLIMITED value for the MAXEXTENTS parameter. The value stored in the MAXEXTENTS attribute in the DBA_TABLES view in this case is still a number: 2147483645.