Oracle7 Server Administrator's Guide

Viewing Information About Tablespaces

The following data dictionary views provide useful information about tablespaces of a database:

USER_EXTENTS, DBA_EXTENTS

USER_SEGMENTS, DBA_SEGMENTS

USER_FREE_SPACE, DBA_FREE_SPACE

DBA_USERS

DBA_TS_QUOTAS

USER_TABLESPACES, DBA_TABLESPACES

DBA_DATA_FILES

V$DATAFILE

The following examples illustrate how to use the views not already illustrated in other chapters of this manual. They assume you are using a database that contains two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1 (100MB) and FILE2 (200MB); the tablespace has been taken offline normally.

Listing Tablespaces and Default Storage Parameters: Example

To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:

SELECT tablespace_name "TABLESPACE",

   initial_extent "INITIAL_EXT",

   next_extent "NEXT_EXT",

   min_extents "MIN_EXT",

   max_extents "MAX_EXT",

   pct_increase

   FROM sys.dba_tablespaces;

TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE

---------- ----------- -------- ------- ------- ------------

SYSTEM        10240000 10240000       1      99           50

USERS         10240000 10240000       1      99           50

Listing the Datafiles and Associated Tablespaces of a Database: Example

To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:

SELECT  file_name, bytes, tablespace_name

   FROM sys.dba_data_files;

FILE_NAME    BYTES      TABLESPACE_NAME

------------ ---------- --------------------

filename1    10240000   SYSTEM

filename2    10240000   USERS

filename3    20480000   USERS

Listing the Free Space (Extents) of Each Tablespace: Example

To see the amount of space available in the free extents of each tablespace in the database, enter the following query:

SELECT tablespace_name, file_id,

   COUNT(*)    "PIECES",

   MAX(blocks) "MAXIMUM",

   MIN(blocks) "MINIMUM",

   AVG(blocks) "AVERAGE",

   SUM(blocks) "TOTAL"

   FROM sys.dba_free_space

WHERE tablespace_name = 'SYSTEM'

GROUP BY tablespace_name, file_id;


TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE     SUM

---------- ------- ------ ------- ------- ------- -------

SYSTEM           1      2    2928     115  1521.5    3043

SUM shows the amount of free space in each tablespace, PIECES shows the amount of fragmentation in the datafiles of the tablespace, and MAXIMUM shows the largest contiguous area of space. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.