Storage Structure Information in the Data Dictionary

The interrelation between the tablespaces, their data files, segments, and free and used extents can be examined by querying the data dictionary. The views that you can use to find information on tablespace are in the following sections.

DBA_TABLESPACES

When a tablespace with one or more files is created, a single row is created in DBA_TABLESPACES. This view provides information that allows you to determine the status, type, and space usage of the tablespace.

DBA_DATA_FILES

For every single file associated with a tablespace in the database, online or offline, a row is added to DBA_DATA_FILES. The following query can be used to find information on the data files associated with the tablespaces in the database:

 SELECT FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS FROM DBA_DATA_FILES where tablespace_name = 'UNDOTBS1'; 

The following shows the output of the query:

TABLESPACE_NAME	FILE_NAME	BYTES	BLOCKS
UNDOTBS1	/mydatabases/mydb1/undotbs01.dbf	209715200	51200

DBA_SEGMENTS

Whenever a segment is created, regardless of the segment type, a row is added to the DBA_SEGMENTS view. From this view, you can determine the number of blocks associated with a segment, the number of extents, to what tablespace the segment belongs, and more.

To obtain information on the number of extents and blocks allocated to a segment, you would execute the following query:

 SELECT SEGMENT_NAME, TABLESPACE_NAME, EXTENTS, BLOCKS FROM DBA_SEGMENTS WHERE OWNER = 'SCOTT'; 

Here is the output you would expect to see from running this query.

SEGMENT_NAME	TABLESPACE_NAME	EXTENTS	BLOCKS
DEPT	SYSTEM	1	16
EMP	SYSTEM	1	16
BONUS	SYSTEM	1	16
SALGRADE	SYSTEM	1	16
PK_DEPT	SYSTEM	1	16
PK_EMP	SYSTEM	1	16
6 rows selected

DBA_EXTENTS

Whenever space is allocated the extents of a segment, that space can be viewed in the DBA_EXTENTS view. Because the extent is the smallest unit of space allocated (although the block is the smallest unit stored or retrieved), whenever an additional extent is allocated to a segment, information about that extent can be found in DBA_EXTENTS.

If you want to get information on the number of blocks and extents for a given segment from this view, you can run the following query:

 SELECT EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS WHERE OWNER = 'SCOTT' AND SEGMENT_NAME = 'EMP'; 

EXTENT_ID	FILE_ID	BLOCK_ID	BLOCKS
0	1	82177	16

DBA_FREE_SPACE

When a tablespace and its data files are added to the database, but before anything is inserted into the objects, all the space in each data file minus the space allocated to the file header, shows up in the DBA_FREE_SPACE view.

When extents are added to a segment, and that space is viewed in DBA_EXTENTS, the space found in DBA_FREE_SPACE is adjusted lower to show the reduced free space in the files where the extents were created for the segment.

All the space in a file, with the exception of that space allocated to the file's header block, must be accounted for in either DBA_FREE_SPACE or DBA_EXTENTS.

DBA_FREE_SPACE is used to check the extents from a given segment.

 SELECT TABLESPACE_NAME, COUNT(*), MAX(BLOCKS), SUM(BLOCKS) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; TABLESPACE_NAME  COUNT(*)  MAX(BLOCKS)  SUM(BLOCKS) 

The following is the output that you might see if running this query.

TABLESPACE_NAME	COUNT	MAX(BLOCKS)	SUM(BLOCKS)
EXAMPLE	1	2640	2640
INDX	1	6384	6384
SYSTEM	1	17120	17120
TOOLS	1	126448	126448
UNDOTBS1	11	35568	45680
USERS	1	6384	6384
XDB	1	2800	2800