Com os scripts abaixo conseguimos reduzir a marca dagua movendo as tabelas que estao
no final dos datafiles. O que fazemos é sempre repetir o script de move table, e reindexando…
Os scripts abaixo geram novos scripts que devem ser rodados..

########################################
Verificar o tamanho do bloco do banco
########################################
set pagesize 1000
set head off

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

##############################################################
Usei o tamanho do bloco de 4096 conforme na consulta abaixo.
Essa consulta ela simplesmente lista quais sao os datafiles
que podem sofrer encolhimento, e gera o comando para ja
reduzir o tamanho conforme a marca dagua.
Essa consulta deve ser repetida toda vez que voce rodar a
consulta que gera o alter table, ou seja movendo a tabela
que esta na marca dagua podemos entao encolher ainda mais
os datafiles. Lembrando que se mover a tabela de lugar
devemos reindexar os indices com status UNUSABLE.
##############################################################

select ‘alter database datafile ”’ ||
file_name || ”’ resize ‘ ||
ceil( (nvl(hwm,1)*4096)/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*4096/1024/1024)-
ceil((nvl(hwm,1)*
4096)/1024/1024 ) > 0
/

##############################################################
Cria o script para fazer o alter table das tabelas que estao
na marca dagua permitindo o encolhimento do datafile,
lembrando que temos que sempre trocar os datafiles
para tentar promover o encolhimento o primeiro que estou
investigando é o ‘/u01/banco/dados01.dbf’
##############################################################
select * from
( select’alter table ‘||owner||’.’||segment_name||’ move tablespace ‘||TABLESPACE_NAME||’;’ dados
from dba_extents
where segment_type = ‘TABLE’
and file_id = ( select file_id from dba_data_files
where file_name = ‘/u01/logix10/dados02.dbf’ )
order by block_id desc )
where rownum <=25;

##############################################################
Cria o script para fazer o rebuild dos indices UNUSABLE
##############################################################
select ‘alter index ‘||owner ||’.’||index_name||’ rebuild tablespace ‘||TABLESPACE_NAME||’;’
from dba_indexes
where status = ‘UNUSABLE’;