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 a40is a SQL*Plus command, not SQL. It sets the display format of thesegment_namecolumn 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 WRAPsetting. 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
owneris the schema that owns the segment.segment_typeis the kind of segment. The documented values includeTABLE,TABLE PARTITION,TABLE SUBPARTITION,INDEX,INDEX PARTITION,INDEX SUBPARTITION,CLUSTER,LOBSEGMENT,LOBINDEX,NESTED TABLE,ROLLBACK,TYPE2 UNDO,TEMPORARYand a few others. There is no barePARTITIONvalue. A partitioned table shows up asTABLE PARTITIONorINDEX PARTITION.segment_nameis 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_extentsis the limit itself, shown beside the spare count so you can see how large the limit was.
The filters and the sort
from dba_segmentsreads the data dictionary view for all segments in the database. TheUSER_SEGMENTSview covers only your own segments and has noOWNERcolumn, 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) < 10keeps 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 4sorts by the fourth column in the select list, which isspare. 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
MAXEXTENTSparameter is valid only for objects in dictionary-managed tablespaces. It also saysMAXEXTENTSis ignored for objects in a locally managed tablespace, unlessALLOCATION_TYPEisUSERfor the tablespace inDBA_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_EXTENTSis 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.
UNLIMITEDremoves the problem. The storage clause documentation describesUNLIMITEDas 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
- DBA_SEGMENTS, Oracle Database 19c Reference - defines the EXTENTS and MAX_EXTENTS columns and the list of SEGMENT_TYPE values used above.
- Storage Clause, Oracle Database 19c SQL Language Reference - states that MAXEXTENTS is valid only in dictionary-managed tablespaces and is ignored in locally managed ones unless ALLOCATION_TYPE is USER; describes UNLIMITED.
- DBA_TABLESPACES, Oracle Database 19c Reference - defines the EXTENT_MANAGEMENT and ALLOCATION_TYPE columns used in the companion queries.
- ORA-01631, Oracle Database Error Help - gives the error text, its cause and the action Oracle recommends.
- Managing Tablespaces, Oracle Database 19c Administrator's Guide - explains that locally managed is the default for new permanent tablespaces.
- COLUMN, SQL*Plus User's Guide and Reference 19c - documents the FORMAT An option and how wrapping is controlled.
- max_extents.sql, DBA scripts at oracle-base.com - a community monitoring script by Tim Hall that reports tables and indexes nearing MAX_EXTENTS, using dba_extents joined to dba_segments.
Posts in this series
- Monitoring Temporary Tablespace Usage in Oracle Database
- Adjusting Segment Growth with MAXEXTENTS in Oracle Database
- Oracle Segments Nearing Max Extents via dba_segments
- Setting a Default Temporary Tablespace in Oracle Database
- Identifying User Tablespaces and Space Usage in Oracle
- Listing Objects in an Oracle Tablespace
- Oracle Tablespaces Needing More Space: SQL Script
- Exploring Datafiles within Oracle Tablespaces
- Understanding Tablespace Usage in Oracle Database
- Understanding User Quotas in Oracle Database
- Creating a Temporary Tablespace in Oracle Database