reclaim disk space from oracle

in database, space was allocated to segment through extents. Extents are bundle of blocks. If a datafile has free blocks after the large-used block_id, it can be resize to give these free blocks back to OS.

below is the script written by Tom Kyte:

-- This script was written by Tom Kyte and retrieved from asktom.oracle.com

set pages 0
set lin 150
set verify off
column file_name format a60 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
column sum format 999,999,999
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a95 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

You might see a lot of free space in a datafile but it can not be given back to OS. These free space are the gap blocks between used blocks in a datafile.

you can use below script to check gaps in a datafile or a tablespace.

-- -----------------------------------------------------------------------------------
-- File Name : http://www.oracle-base.com/dba/monitoring/ts_extent_map.sql
-- Author : DR Timothy S Hall
-- Description : Displays gaps (empty space) in a tablespace or specific datafile.
-- Requirements : Access to the DBA views.
-- Call Syntax : @ts_extent_map (tablespace-name) [all | file_id]
-- Last Modified: 25/01/2003
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF

DECLARE
l_tablespace_name VARCHAR2(30) := UPPER('&1');
l_file_id VARCHAR2(30) := UPPER('&2');

CURSOR c_extents IS
SELECT owner,
segment_name,
file_id,
block_id AS start_block,
block_id + blocks - 1 AS end_block
FROM dba_extents
WHERE tablespace_name = l_tablespace_name
AND file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))
ORDER BY file_id, block_id;

l_block_size NUMBER := 0;
l_last_file_id NUMBER := 0;
l_last_block_id NUMBER := 0;
l_gaps_only BOOLEAN := TRUE;
l_total_blocks NUMBER := 0;
BEGIN
SELECT block_size
INTO l_block_size
FROM dba_tablespaces
WHERE tablespace_name = l_tablespace_name;

DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);
FOR cur_rec IN c_extents LOOP
IF cur_rec.file_id != l_last_file_id THEN
l_last_file_id := cur_rec.file_id;
l_last_block_id := cur_rec.start_block - 1;
END IF;

IF cur_rec.start_block > l_last_block_id + 1 THEN
DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' || cur_rec.start_block || ')' ||
' FileID=' || cur_rec.file_id ||
' Blocks=' || (cur_rec.start_block-l_last_block_id-1) ||
' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)
);
l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;
END IF;
l_last_block_id := cur_rec.end_block;
IF NOT l_gaps_only THEN
DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||
' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks * l_block_size)/1024/1024,2));
END;
/

PROMPT
SET FEEDBACK ON

How can you reclaim the gap blocks?

1. export the object that used the datafile. Then resize the datafile, import it back.
2. move the object to other tablespace then resize the datafile. move it back. rebuild index.

all above methods make the object unavailable during the procedure, but they definite will work.

3. alter table t move. rebuild indexes. this will make the table unvailable too, but it may not work since there is no guarant for extent change. It save one object move.

4. alter table t shrink space compact. it might work since there is no guarant for extent change. However it will keep the object online, however you have to enable row movement for the table which will cause chain migration.


reference:
http://www.dba-oracle.com/t_reclaiming_disk_space.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7246820117571
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:531274103649
http://www.dba-oracle.com/t_alter_table_move_shrink_space.htm