Query EXTENT TABLE Oracle

Published by Patrizia Gardis on 5 March 20195 March 2019

With the article Query EXTENT TABLE Oracle, we provide a dynamic query to manage the extents of Oracle objects.

Additionally, if an object does not have the possibility to extend, we will verify if increasing the number of extents is necessary.

The smallest unit of measurement within the database is the block size, which ranges from 2k to 16k, with the default being 8k. The size of an EXTENT is interpreted by Oracle as a number of block sizes, from which the extent size is determined.

Therefore, we have the ability to manage the extent size.

Sometimes, however, it may happen that the table cannot extend because it has reached the maximum number of extents it can reach, indicated by the Oracle database in the alert log with the following error message:

ORA-01631: max # extents (404) reached in table USTEST.TAB_PROVA

In these cases, we can execute the following query to identify the maximum number and then populate the MAX_EXTENTS field in the command that will be returned by the following statement.

Query EXTENT TABLE Oracle to increase the number of extents:

set lines 300
select 'alter '||SEGMENT_TYPE||' '||OWNER||'.'||SEGMENT_NAME||' storage ( maxextents &MAX_EXTENTS);'
from dba_segments
where SEGMENT_NAME = '&NOME_SEG';

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

• Query EXTENT TABLE Oracle
• Query Oracle objects with few EXTENTS
• Query EXTENT Oracle number and size