Query Oracle objects with few EXTENTS
With the article Query Oracle objects with few EXTENTS, we provide a query to display information about objects with less than 100 EXTENTS.
As explained in the article Query EXTENT TABLE Oracle,the EXTENTS of a table have a well-defined number.
Once this limit is reached, the following error message is generated in the database alert log:
ORA-01631: max # extents (404) reached in table USTEST.TEST_TAB
To verify if there are indeed objects with a shortage of EXTENTS, we provide a suitable query.
The selected fields are:
OWNER – Owner of the Oracle object.
SEGMENT_NAME – Name of the Oracle object.
SEGMENT_TYPE – Type of Oracle object.
BYTES – Size of the Oracle object converted to MB.
NEXT_EXTENT – Size of an object’s extent.
EXTENTS – Number of extents that the object has reached.
MAX_EXTENTS – Maximum number of extents that an object can reach.
The selected information is extracted from the DBA_SEGMENTS view, and in the WHERE condition, we calculate the remaining extents by subtracting the current number from the maximum number of extents.
Finally, once the remaining extents are retrieved, we insert < 100 as the operator.
Query Oracle objects with few EXTENTS
set lines 200
col OWNER for a12
col SEGMENT_NAME for a32
col SEGMENT_TYPE for a16
select owner
, segment_name
, segment_type
, BYTES/1024/1024 MB_ATT
, NEXT_EXTENT/1024/1024 MB_NEXT
, extents
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 100
order by 2
/To view my guides on CRS commands click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining on our community.
Facebook
Youtube
Linkedin
0 Comments