Oracle – Shrink de tabelas dentro da Tablespace (redução)

– Enable row movement.
ALTER TABLE hr.teste ENABLE ROW MOVEMENT;

– Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

– Recover space, but don’t amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

– Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

– Disable row movement.
alter table HR.TESTE disable row movement;

O Select abaixo, efetua a geração dos scripts para redução e realocação do espaço livre nas tabelas:

select t.*,
'ALTER ' || t.segment_type || ' ' || t.segment_name || ' ENABLE ROW MOVEMENT;' as enable_row_mov,
'ALTER ' || t.segment_type || ' ' || t.segment_name || ' SHRINK SPACE CASCADE;' as shrink,
'ALTER ' || t.segment_type || ' ' || t.segment_name || ' DISABLE ROW MOVEMENT;' as disable_row_mov
from (
select segment_name, segment_type, bytes/1024/1024 Size_Mb
from user_segments
where segment_type = 'TABLE'
and not exists(
select 1
from user_tab_columns
where table_name = segment_name
and data_type IN ('LONG', 'LONG RAW'))
order by bytes/1024/1024 DESC ) t;

fonte:
http://www.superti.org/?p=122
http://veduardodba.wordpress.com/2011/12/29/shrink-a-table/