Wednesday, June 11, 2008

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
 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

No comments:

Post a Comment