***Checked for relevance on 15-Jun-2012*** 

The information in this article applies to: Oracle 8.1.7 to 11.2.0.3

PURPOSE
-------

Once the database is up and running, it is always good practice to monitor the
growth of the system. 

 
SCOPE & APPLICATION
-------------------

If you can anticipate how much more space a particular
database object will need, then you can plan where to acquire the additional
space.These 3 scripts will give the DBA a chance to plan for additional disk space if 
it is currently not available on the system.


DISCLAIMER
----------
 
This script is provided for educational purposes only. It is NOT supported by 
Oracle World Wide Technical Support.  The script has been tested and appears  
to work as intended.  However, you should always test any script before  
relying on it. 


SCRIPTS
-------
----------- cut ---------------------- cut -------------- cut -------------- 

=============================
 SCRIPT 1 : EXTENT_CHECK.sql 
=============================

- SQL to determine all database objects that have acquired at least a certain
number of extents. The ORACLE_SID and the number of extents to check out are
passed in from the command line.


rem OUTPUT FILENAME 
rem  
rem     extent_check_ORACLE_SID.lis 
rem  
rem PARAMETERS 
rem  
rem     1       ORACLE_SID      SID of the current database 
rem     2       WARN_EXTENTS    Warning level for maximum extents  
rem      
rem USAGE 
rem 
rem     sqlplus dba_user/password @check_extents.sql ORACLE_SID 20  
rem  
define ORACLE_SID=&1    /* ORACLE_SID passed in from command line       */ 
define WARN_EXTENTS=&2  /* Number of extents passed in from command line*/ 
  
set termout off 
set newpage 0 
set pagesize 65 
set linesize 80  
set feed off 
set verify off 
set wrap off 
 
ttitle - 
 skip 2 - 
 left _sysdate - 
 right format 9,999 'Page: ' sql.pno - 
 skip 2 - 
 center 'Check Extent for ' ORACLE_SID - 
 skip 1 - 
 center 'Extents over: ' WARN_EXTENTS - 
 skip 2 
 
break on owner 
 
column  sys_date new_value _sysdate noprint 
column  owner                   format a12         heading "OWNER" 
column  segment_name            format a25 trunc   heading "SEGMENT|NAME" 
column  segment_type            format a10 trunc   heading "SEGMENT|TYPE" 
column  kbytes                  format 9,999,999   heading "KBYTES" 
column  extents                 format 9,999,999   heading "EXTENTS" 
column  max_extents             format 99999       heading "MAX|EXTENTS" 
select  to_char(sysdate,'Dy DD-Mon-YY HH:MI PM') SYS_DATE, 
        ds.owner , ds.segment_name , ds.segment_type ,   
        ds.bytes/1024   kbytes , ds.extents, ds.max_extents 
from    sys.dba_segments        ds 
where   ds.extents > &&WARN_EXTENTS 
order by ds.extents desc , ds.owner 
 
spool extent_check_&ORACLE_SID..lis 
 
/ 
 
spool off 
 
undefine ORACLE_SID 
undefine WARN_EXTENTS 
 
exit 

 
----------- cut ---------------------- cut -------------- cut -------------- 

EXEMPLE
-------
 
A sample output for ORACLE_SID S6F at extents of 5 or more: 
 
Fri 21-Jun-01 12:23 PM                                              Page:       
1 
 
                                Check Extent S6F 
                                 Extents over: 5 
 
             SEGMENT                   SEGMENT                          MAXIMUM 
OWNER        NAME                      TYPE           KBYTES    EXTENTS EXTENTS 
------------ ------------------------- ---------- ---------- ---------- ------- 
SYS          C_OBJ#                    CLUSTER           652          9      99 
             I_COL1                    INDEX             232          7      99 
             VIEW$                     TABLE             150          6      99 


 
----------- cut ---------------------- cut -------------- cut --------------  

==================================
 SCRIPT 2 : CONTIG_FREE_SPACE.sql
==================================

- PL/SQL script to determine how much contiguous  
free space is available for each tablespace in the database.  Oracle will  
acquire space by searching first for exact fit, then next best fit, and lastly  
coalesce if possible.  


 
create table SPACE_TEMP ( 
 TABLESPACE_NAME        CHAR(30), 
 CONTIGUOUS_BYTES       NUMBER) 
/ 
 
declare 
  cursor query is select * 
                  from dba_free_space 
                  order by tablespace_name, block_id; 
  this_row        query%rowtype; 
  previous_row    query%rowtype; 
  total           number; 
 
begin 
  open query; 
  fetch query into this_row; 
  previous_row := this_row; 
  total := previous_row.bytes; 
  loop 
     fetch query into this_row; 
     exit when query%notfound; 
     if this_row.block_id = previous_row.block_id + previous_row.blocks then 
        total := total + previous_row.bytes; 
        insert into SPACE_TEMP (tablespace_name) 
                  values (previous_row.tablespace_name); 
     else 
        insert into SPACE_TEMP values (previous_row.tablespace_name, 
                                 total); 
        total := this_row.bytes; 
     end if; 
     previous_row := this_row; 
  end loop; 
  insert into SPACE_TEMP values (previous_row.tablespace_name, 
                           total); 
end;  
/ 
 
set pagesize 60 
set newpage 0 
set echo off 
ttitle center 'Contiguous Extents Report' - 
       skip 1 center new_today skip 3 
break on "TABLESPACE NAME" skip page duplicate 
spool contig_free_space.lis 
rem 
column "CONTIGUOUS BYTES"       format 999,999,999 
column "COUNT"                  format 999 
column "TOTAL BYTES"            format 999,999,999 
column "TODAY"   noprint new_value new_today format a1 
rem 
select TABLESPACE_NAME  "TABLESPACE NAME", 
       CONTIGUOUS_BYTES "CONTIGUOUS BYTES", 
       count(*)         "COUNT", 
       CONTIGUOUS_BYTES*count(*) "TOTAL BYTES", 
       to_char(sysdate,'FMMonth DD, YYYY') "TODAY" 
from SPACE_TEMP 
where CONTIGUOUS_BYTES is not null 
group by TABLESPACE_NAME, CONTIGUOUS_BYTES 
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc 
/ 
spool off 
 
drop table SPACE_TEMP 
/ 
 
exit 
/ 


 
----------- cut ---------------------- cut -------------- cut --------------  
A sample output: 
                            Contiguous Extents Report 
                                   May 28, 2001
 
 
TABLESPACE NAME                CONTIGUOUS BYTES COUNT  TOTAL BYTES 
------------------------------ ---------------- ----- ------------ 
TEMP                                  3,584,000     1    3,584,000 
TEMP                                  1,781,760     1    1,781,760 
TEMP                                  1,740,800     1    1,740,800 
TEMP                                  1,536,000     1    1,536,000 
TEMP                                  1,126,400     2    2,252,800 
TEMP                                    716,800     1      716,800 
TEMP                                    512,000     1      512,000 
 


Combining the contiguous extents in the tablespace yields eight large 
virtual extents, two of which are the same size (1,126,400 bytes).   
 
If many rows are returned with small contiguous bytes and count of 1, then the 
tablespace may be fragmented.  Currently, the tools to remedy fragmentation 
are IMP/EXP (import and export) utilities.  You may also drop segments that 
lie between free extents, create a dummy object that will require a segment 
of just the right size to cause the separate segments to be coalesced, and 
then drop the dummy object. 

 
----------- cut ---------------------- cut -------------- cut -------------- 

================================
 SCRIPT 3 : GET_NEXT_EXT_SIZE.c 
================================
 
- C program to calculate the size of the next extent 
based on pctincrease and next extent size for the object. 
 
/************************************************************************** 
   
  The program requires 3 arguments: 
 
        arg1    pctincrease of the object 
        arg2    number of the extent you wish to calculate the size of 
        arg3    size of the next extent storage parameter 
 
  For example: 
 
        get_next_ext_size 20 3 10000 
 
  This will return the size of extent# 4 for an object that has a 
  percent increase of 20% and next extent size of 10,000 bytes. 
 
**************************************************************************/ 
#include  
main(argc,argv) 
  int argc; 
  char **argv; 
{ 
  int i, pctincrease, next_extent_size, next_extent_no; 
  float inc; 
 
  pctincrease = atoi(argv[1]); 
  next_extent_no = atoi(argv[2]); 
  next_extent_size = atoi(argv[3]); 
 
  for ( i = 1 ; i < next_extent_no; i++ ) { 
    inc = (next_extent_size*pctincrease)/100; 
      if((next_extent_size*pctincrease)%100)  
        inc++; /* round up */ 
    next_extent_size += inc; 
  } 
  printf("The size of extent# %d will be %d blocks.\n", 
          next_extent_no,next_extent_size); 
} 
 

From the information retrieved in the above three scripts, you can track the 
objects in each tablespace and make sure there is enough space for it to grab 
at least another extent.  Also, it is important to make sure the object is not 
getting close to the maximum number extents as the database increases in size. 
 

RELATED DOCUMENTS
----------------- Note:10640.1 Extent and Block Space Calculation and Usage in V7 Database