In our datawarehouse we often run into the "problem" that tablespaces contain a lot of free space within the tablespace itself. Most of the time, this is caused by the fact that the warehouse programs first build up new objects and afterwards destroy old objects.
In the future we might need to have a more definitive solution for this, like converting the tablespaces to one with an uniform size.
However until then I use to do a tablespace reorganization. This means I pick a block_id of the tablespace datafile, somewhere around the point of total usage.
For this procedure to work, you need a tablespace that can temporary store the objects that lie beyond the free space in the tablespace you want to reclaim. This temporary store tablespace should NOT be an oracle temporary tablespace, but just a permanent tablespace that temporary can hold these objects.
It is preferred to create a seperate tablespace for this, as this can afterwards be removed again.
How the reorganizing works:
E.g If we have a tablespace of 10Gb in size and its datafile ( let's assume we have a single file ), has 4Gb of free space, I use the following query to determine the upper-bound block_id:
SQL> select /*+ rule */
from dba_extents a,
where a.tablespace_name='USERS' ;
If a tablespace has more datafiles, then you need to specify a certain datafile by adding the the file_id column in the query. In that case first look up the datafile number by joining v$tablespace and v$datafile.
After the block_id has been calculated, I use the following complex query to generate table move and index rebuild statements:
set pages 9999 lines 200
accept "Reorg tablespace : " &tabsource;
accept "Temporary store tablespace (not TEMP) : " &tabtemp;
select distinct cmd from
(SELECT /*+ rule */
|| DECODE (segment_type
, 'TABLE SUBPARTITION', 'TABLE'
, 'TABLE PARTITION', 'TABLE'
, 'INDEX', 'INDEX'
, 'INDEX PARTITION', 'INDEX'
, 'TABLE' -- TABLE is the default
|| ' '
|| DECODE (segment_type, 'INDEX', 'REBUILD','INDEX PARTITION','REBUILD','MOVE')
|| ' '
|| DECODE (segment_type
, 'TABLE SUBPARTITION', 'SUBPARTITION'
, 'TABLE PARTITION', 'PARTITION'
, 'INDEX PARTITION','PARTITION')
|| ' '
|| ' tablespace '
|| DECODE (segment_type, 'INDEX', ' ONLINE','INDEX PARTITION',' ONLINE')
|| ';' cmd
WHERE tablespace_name = &tabsource
AND block_id > [Calulated Block_size]) ;
In the above query the use of FILE_ID is commented out. This because we assumed that we have only one datafile. Otherwise enter the file_id that you've found in v$datafile;
The result of this query is a lot of object-rebuild statements, that move tables and tablepartitions to the tablespace defined by &tabtemp. The indexes and index-partitions are online rebuild in the same tablespace.
Remember to SAVE the script, as you need it to move everything back to the original tablespace.
Run the script outside business hours, as moving table objects will lock the tables, invalidate indexes and all dependent packages/procedures.
As soon as the script is finished, purge the tablespace of its 'recyclebin' contents, by issuing as SYS:
SQL> purge tablespace USERS;
Afterwards the tablespace datafile can be shrunk to the desired size, preferably subtracting the size of the empty space in the beginning from the total datafile size.
As soon as that is finished, reopen the script you saved, edit it and change back all tablespace names, to the original source tablespace ( in this example USERS ).
Then rerun the script .
Finally use UTLRP to rebuild all invalidated packages/procedure and indexes. Also, if you created a temporary tablespace for this procedure, drop that again.
In our databases, this procedure usually frees lots of free space. Last week also I was able to return 29Gb of free space back to the OS.
Copyright: This script can be freely used. If you use it as an explanation on your own site/blog, a link to the source (my blog) is appreciated.