Query EXTENT Oracle number and size

Published by Patrizia Gardis on

With the article Query EXTENT Oracle number and size, we have the opportunity to view many useful pieces of information for managing the extents of a table.

The retrieved information includes the table size, extent size, the current number of extents, and the maximum number that extents can reach.

As explained in the article Query EXTENT TABLE Oracle, the number of extents has a maximum limit that it can reach, which is manageable through the extent size and the maximum number of extents that the Oracle TABLE can reach.

These pieces of information can be verified with the query provided in the following article.

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 filtered by the object name.

Number and size of extents of an object:

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 segment_name='&Object_name'
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

Leave a Reply

Avatar placeholder