Oracle Segments Nearing Max Extents via dba_segments

Identify Oracle Database Segments Nearing Their Maximum Extent Limit

Oracle ignores the MAXEXTENTS storage parameter for objects in a locally managed tablespace, unless the tablespace has ALLOCATION_TYPE set to USER. That one rule decides whether the query below is useful on your database. It is a real early warning in a dictionary-managed tablespace. In a default, locally managed tablespace it will usually return nothing, because the limit it measures is not enforced.

Purpose

DBA_SEGMENTS describes the storage allocated for all segments in the database. Two of its columns are EXTENTS, the number of extents allocated to the segment, and MAX_EXTENTS, the maximum number of extents allowed in the segment. Subtract one from the other and you get the number of extents a segment can still add before Oracle refuses to grow it.

The query below lists every segment, outside the SYS and SYSTEM schemas, that has fewer than 10 extents left. The segment closest to its limit is shown first. The goal is to find these segments while there is still time to act, instead of finding them through an error in a user session.

The error in question is ORA-01631, "max # extents reached in table". Oracle documents its cause as a table that tried to extend past MAXEXTENTS. Because the check is about a limit, it only applies where the limit is enforced. The sections below show how to tell whether that is true for your tablespaces.

Code

 1col segment_name format a40
 2select owner
 3, segment_type
 4, segment_name
 5, max_extents - extents as "spare"
 6, max_extents
 7from dba_segments
 8where owner not in ('SYS','SYSTEM')
 9and (max_extents - extents) < 10
10order by 4
11/

Code Breakdown

The statement is a single query against one view. Each line has one job.

The SQL*Plus formatting line

  • col segment_name format a40 is a SQL*Plus command, not SQL. It sets the display format of the segment_name column to an alphanumeric column of width 40.
  • SQL*Plus documents that when a character value does not fit the column width, it wraps or truncates the string depending on the SET WRAP setting. A long segment name may therefore wrap or be cut at a width of 40.
  • If you run the query from another client, drop this line. Other tools ignore it or report an error.

The select list

  • owner is the schema that owns the segment.
  • segment_type is the kind of segment. The documented values include TABLE, TABLE PARTITION, TABLE SUBPARTITION, INDEX, INDEX PARTITION, INDEX SUBPARTITION, CLUSTER, LOBSEGMENT, LOBINDEX, NESTED TABLE, ROLLBACK, TYPE2 UNDO, TEMPORARY and a few others. There is no bare PARTITION value. A partitioned table shows up as TABLE PARTITION or INDEX PARTITION.
  • segment_name is the name of the segment.
  • max_extents - extents as "spare" is the number of extents the segment can still add. The alias is quoted so it prints in lower case.
  • max_extents is the limit itself, shown beside the spare count so you can see how large the limit was.

The filters and the sort

  • from dba_segments reads the data dictionary view for all segments in the database. The USER_SEGMENTS view covers only your own segments and has no OWNER column, so it is not a drop-in replacement.
  • where owner not in ('SYS','SYSTEM') leaves out the two main administrative schemas. Other Oracle-maintained schemas are not excluded, so they can still appear in the output.
  • and (max_extents - extents) < 10 keeps only segments with fewer than 10 spare extents. The number 10 is a choice made in the script, not a figure from Oracle. Change it to suit your environment.
  • order by 4 sorts by the fourth column in the select list, which is spare. The default direction is ascending, so the smallest spare count comes first.
  • The closing / runs the statement in SQL*Plus.

Key Points

  • The limit is not always enforced. The Oracle SQL reference says the MAXEXTENTS parameter is valid only for objects in dictionary-managed tablespaces. It also says MAXEXTENTS is ignored for objects in a locally managed tablespace, unless ALLOCATION_TYPE is USER for the tablespace in DBA_TABLESPACES.
  • Locally managed is the default. The Administrator's Guide says locally managed is the default for new permanent tablespaces. If your tablespaces were created with defaults, this query has little to find.
  • A row is not proof of risk. MAX_EXTENTS is shown for every segment, including those in locally managed tablespaces. A low spare count there does not mean ORA-01631 is coming. Check the tablespace first.
  • The query reports only. It changes nothing. Any follow-up is yours to decide and run.
  • UNLIMITED removes the problem. The storage clause documentation describes UNLIMITED as a setting that lets extents be allocated automatically as needed, and says Oracle recommends it as a way to minimize fragmentation. The same page warns against it for rollback segments.

Insights and Best Practices

Check the tablespace type before you trust the output

DBA_TABLESPACES has an EXTENT_MANAGEMENT column. It tells you whether extents are dictionary managed (DICTIONARY) or locally managed (LOCAL). It also has an ALLOCATION_TYPE column with the values SYSTEM, UNIFORM and USER. Run this first:

1select tablespace_name
2, extent_management
3, allocation_type
4from dba_tablespaces
5order by 1;

If every row shows LOCAL and none shows USER, the extent limit is not enforced anywhere, and the original query is a historical check. If some rows show DICTIONARY, those tablespaces are where the check still matters.

A version of the query that follows the rule

The statement below is an addition to the original script. It joins DBA_SEGMENTS to DBA_TABLESPACES and keeps only segments in tablespaces where the documented rule says MAXEXTENTS applies. It was written from the column definitions in the Oracle Database Reference and has not been run against a live instance, so test it on a non-production database first.

 1select s.owner
 2, s.segment_type
 3, s.segment_name
 4, s.tablespace_name
 5, s.max_extents - s.extents as spare
 6, s.max_extents
 7from dba_segments s
 8join dba_tablespaces t
 9  on t.tablespace_name = s.tablespace_name
10where s.owner not in ('SYS','SYSTEM')
11and (t.extent_management = 'DICTIONARY' or t.allocation_type = 'USER')
12and (s.max_extents - s.extents) < 10
13order by spare;

The OR follows the documented rule. A segment is subject to MAXEXTENTS when its tablespace is dictionary managed, or when its ALLOCATION_TYPE is USER. This version removes the false alarms from locally managed tablespaces.

Choose a threshold from growth, not from habit

The value 10 is only a starting point. A segment that adds one extent a month has a long runway at 10 spare extents. A segment that adds several extents a day does not. If you keep a history of extent counts, compare two runs of the query and set the threshold from the observed rate of growth.

Know what Oracle tells you to do

The action text for ORA-01631 is short. If MAXEXTENTS is less than the system maximum, raise it. Otherwise, you must recreate the object with larger initial, next or pctincrease parameters. The first option changes a setting. The second means recreating the object, so plan it before you start.

When to Use This Check

  • After you inherit an older database, to find out whether any tablespace is still dictionary managed.
  • Before a data load into a table that lives in a dictionary-managed tablespace.
  • When ORA-01631 has already appeared once and you want to find other segments in the same state.
  • As a scheduled report, if your site still has dictionary-managed tablespaces.
  • After a migration of a tablespace from dictionary managed to locally managed, to confirm that no segment still depends on the old limit.

Troubleshooting Common Issues

The query returns no rows. This is the expected result on a database where all tablespaces are locally managed, and it does not mean the query is broken. Run the DBA_TABLESPACES query above to confirm.

The query fails because DBA_SEGMENTS cannot be read. Your account does not have access to that view. Ask your DBA for access to the dictionary views, or switch to USER_SEGMENTS for your own objects and drop the owner column from the select list and filter.

A segment shows a low spare count but no error has occurred. Look up the segment's TABLESPACE_NAME in DBA_SEGMENTS, then its EXTENT_MANAGEMENT and ALLOCATION_TYPE in DBA_TABLESPACES. In a locally managed tablespace with an ALLOCATION_TYPE other than USER, the stored MAX_EXTENTS is not used as a limit.

An error appears about space, but the query shows nothing. ORA-01631 is only one of the errors for a segment that cannot grow. The Administrator's Guide lists the case where "the operation cannot acquire any more extents" and gives ORA-01653, "unable to extend table", as an example. It is a different error from ORA-01631, so check the error code before you look at the extent limit.

Long names are cut off or wrapped. Raise the width in col segment_name format a40, or change the SET WRAP setting in SQL*Plus.

References

Posts in this series