Using DBA_FREE_SPACE

Oracle Metalink 121259.1

View DBA_FREE_SPACE can be used to determine the space available in the database.

Sometimes the DBA does not know why Oracle is unable to allocate a new extent and for a quick solution he proceeds to add a new datafile.  Not always is this solution is the best.  Oracle has some views that help to determine which solution is better.  These views are:

DBA_FREE_SPACE:  lists the free extents in all tablespaces

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace containing the extent
FILE_ID	NUMBER	NOT NULL	ID number of the file containing the extent
BLOCK_ID	NUMBER	NOT NULL	Starting block number of the extent
BYTES	NUMBER		Size of the extent in bytes
BLOCKS	NUMBER	NOT NULL	Size of the extent in Oracle blocks
RELATIVE_FNO	NUMBER	NOT NULL	Relative file number of the first extent block

DBA_FREE_SPACE_COALESCED: This view help to determine how coalesce is a tablespace.

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of tablespace
TOTAL_EXTENTS	NUMBER		Total number of free extents in tablespace
EXTENTS_COALESCED	NUMBER		Total number of coalesced free extents in tablespace
PERCENT_EXTENTS_COALESCED	NUMBER		Percentage of coalesced free extents in tablespace
TOTAL_BYTES	NUMBER		Total number of free bytes in tablespace
BYTES_COALESCED	NUMBER		Total number of coalesced free bytes in tablespace
TOTAL_BLOCKS	NUMBER		Total number of free Oracle blocks in tablespace
BLOCKS_COALESCED	NUMBER		Total number of coalesced free Oracle blocks in tablespace
PERCENT_BLOCKS_COALESCED	NUMBER		Percentage of coalesced free Oracle blocks in tablespace

Errors such as:

ORA-1653 unable to extend table by in tablespace

ORA-1654 unable to extend index by for tablespace

ORA-1655 unable to extend cluster e by for tablespace

ORA-1658 unable to create INITIAL extent for segment in tablespace %s

ORA-1659 unable to allocate MINEXTENTS beyond in tablespace

indicate to DBA there is not free extent in the tablespace reported to support the new extent.  Consulting the view DBA_FREE_SPACE the DBA can know if really the tablespace does not have space available or the tablespace is fragmented and reorganization should be made.  Remember that two contiguous extents are considered two free spaces and both spaces can not be neither summarized nor counted as one for free space contiguous.

A simple query to get information about the free space in a tablespace is:

SELECT TABLESPACE_NAME, BLOCK_ID, BLOCKS, BYTES FROM DBA_FREE_SPACE;

Depending on the problem, the solution to apply can vary from adding new datafile to recreating the tablespace in order to defragment it.  To know which is the problem, the DBA should look up on DBA_FREE_SPACE and check how freed is the tablespace.

If the tablespace have no space avaliable, the solution can be:

1.  Add a new datafile using ALTER TABLESPACE command.

2.  Resize the current datafile using ALTER DATABASE command.

If the tablespace is fragmented the only solution is to reorganize the tablespace.

Some useful scripts:

--SPACE AVAILABLE IN TABLESPACES          select a.tablespace_name,        round(sum(a.tots)/1024/1024) Tot_Size_MB,          round(sum(a.sumb)/1024/1024) Tot_Free_MB,         round(sum(a.sumb)*100/sum(a.tots)) Pct_Free,          round(sum(a.largest)/1024/1024) Max_Free_MB,        sum(a.chunks) Chunks_Free   from   (   select tablespace_name, 0 tots, sum(bytes) sumb,           max(bytes) largest, count(*) chunks   from   DBA_FREE_SPACE a   group by tablespace_name   union   select tablespace_name, sum(bytes) tots, 0, 0, 0  from   DBA_DATA_FILES    group by tablespace_name) a   group by a.tablespace_name;

--SEGMENTS WITH MORE THAN 20 EXTENTS          select owner, segment_name, extents,        round(bytes/1024/1024) size_MB,          max_extents, next_extent   from   DBA_SEGMENTS    where  segment_type in ('TABLE','INDEX') and extents > 20   order by owner, segment_name;

Last updated: 2009-11-30 Monday