/*
USER_EXTENTS describes the extents comprising the segments owned by the current user’s objects. This view does not display the OWNER, FILE_ID,
BLOCK_ID, or RELATIVE_FNO columns.
Column Datatype NULL Description
—————————————————————————————————————
OWNER VARCHAR2(30) Owner of the segment associated with the extent
SEGMENT_NAME VARCHAR2(81) Name of the segment associated with the extent
PARTITION_NAME VARCHAR2(30) Object Partition Name (Set to NULL for non-partitioned objects)
SEGMENT_TYPE VARCHAR2(18) Type of the segment: INDEX PARTITION, TABLE PARTITION
TABLESPACE_NAME VARCHAR2(30) Name of the tablespace containing the extent
EXTENT_ID NUMBER Extent number in the segment
FILE_ID NUMBER File identifier number of the file containing the extent
BLOCK_ID NUMBER Starting block number of the extent
BYTES NUMBER Size of the extent in bytes
BLOCKS NUMBER Size of the extent in Oracle blocks
RELATIVE_FNO NUMBER Relative file number of the first extent block
*/
set linesize 149
accept onrnm prompt ‘Enter Owner Name : ‘
accept segnm prompt ‘Enter Segment Name : ‘
column owner format a4
column segment_name format a25
column extent_id format 9999
column file_name format a40
column tablespace_name format a10
column block_id format 999999 heading ‘start|blkid’
column size_in_kb format 999999
column size_in_blocks format 999999
select a.owner, a.segment_name, a.extent_id, b.file_name, a.tablespace_name, a.block_id , a.bytes/(1024 * 1024) size_in_MB, a.blocks size_blks
from dba_extents a, dba_data_files b
where a.file_id = b.file_id
and owner = upper(‘&onrnm’)
and segment_name = upper(‘&segnm’)
order by extent_id;
set lines 80