Title: Filling Your Small Free Extents

Author: Laszlo Jagusztin a DBA group leader, for TITASZ Inc.,in Debrecen Hungary

Every database has a lot of free extents that you are unable to use because they are too small for ordinary next extents sizes.
For example, in my 65 GB database I have 2 GB of wasted space. With the script below, you can examine how much wasted
space is in your database. Then you can allocate that space to a table or index that needs more space and the performance of
that table is not so critical (it will have a lot of fragmented extents).

--  Author:     Laszlo Jagusztin, TITASZ Inc. Hungary
--  Function:   Allocates small free extents in a tablespace to an existing
--              table or index
--  Note:       Set corretly the MIN_EXTENT_SIZE and MAX_EXTENT_SIZE
--              parameter that sets how much free space will be filled
--              in the tablespace


define OBJECT_NAME="scott.emp"  /* table or index name use for allocation*/
define OBJECT_TYPE="table"      /* object type: table or index*/
define TABLESPACE="'DATA'"      /* tablespace name you want to fill */
define MIN_EXTENT_SIZE=40960    /* minimum fillable extent size (bytes)*/
define MAX_EXTENT_SIZE=40960000 /* maximum fillable extent size (bytes)*/

set pagesize 0
set linesize 512
set echo     off
set feedback off
set termout  off
set verify   off

/* execute this script to do the free extent allocation */
spool allocate.sql

select 'alter &OBJECT_TYPE &OBJECT_NAME allocate extent (size '||f.bytes||
       ' datafile '''||name||''');'
      from  dba_free_space f, v$datafile d
      where (f.bytes>=&MIN_EXTENT_SIZE and
             f.bytes<=&MAX_EXTENT_SIZE and
             tablespace_name=&TABLESPACE and file_id=file#)
      order by f.bytes desc;

spool off
set termout on

select 'Allocatable Free Space (bytes):'||sum(bytes) from dba_free_space
       where (bytes>&MIN_EXTENT_SIZE and
              bytes<&MAX_EXTENT_SIZE and
              tablespace_name=&TABLESPACE);

set echo     off
set feedback off
set verify   off