DBA_EXTENTS

Oracle 11gR1

DBA_EXTENTS describes the extents comprising the segments in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.

Related View

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

Note:

1. Use the query below to find this objects that is in a datafile. It lists all the segments contained in that datafile, the blockid where it starts, and how many blocks the segment contains. It shows the owner, segment name, and segment type.

select

owner ownr,

segment_name name,

segment_type type,

extent_id exid,

file_id fiid,

block_id blid,

blocks blks

from

dba_extents

where

file_id = 10

order by

block_id;

OWNER	SEGMENT_NAME	SEGMENT_TYPE	EXTENT_ID	FILE_ID	BLOCK_ID	BLOCKS
SDE	A101	TABLE	0	10	9	8
SDE	A101	TABLE	1	10	17	8
SDE	A101	TABLE	2	10	25	8
SDE	A101	TABLE	3	10	33	8
SDE	A101	TABLE	4	10	41	8

Oracle data dictionary views