Levantar informações para comparar após a manutenção:
Tamanho atual da tabela
SELECT bytes/1048576 MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'TESTE' AND
SEGMENT_NAME = 'TB_LOG' AND
SEGMENT_TYPE = 'TABLE';
/Tamanho real do lob:
SELECT nvl((sum(dbms_lob.getlength( arquivo ))),0)/1048576 AS mb FROM TESTE.TB_LOG;
Realizar shrink da tabela lob:
ALTER TABLE TESTE.TB_LOG ENABLE ROW MOVEMENT;
ALTER TABLE TESTE.TB_LOG MODIFY LOB(arquivo) (SHRINK SPACE CASCADE);
ALTER TABLE TESTE.TB_LOG SHRINK SPACE COMPACT CASCADE;
ALTER TABLE TESTE.TB_LOG DISABLE ROW MOVEMENT;
EXEC dbms_stats.gather_table_stats(ownname => 'TESTE', tabname => 'TB_LOG', method_opt=> 'for all indexed columns size skewonly', granularity => 'ALL', degree => 8 ,cascade => true,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Mover tabela para outra tablespace:
ALTER TABLE TESTE.TB_LOG move TABLESPACE TBS_TESTE;
ALTER TABLE TESTE.TB_LOG move TABLESPACE TBS_PRODUCAO;Caso algum index fique invalido:
select 'ALTER INDEX TESTE.' || index_name || ' REBUILD TABLESPACE TBS_PRODUCAO;'
from dba_indexes where owner='TESTE'
and table_name = 'TB_LOG'
and status='UNUSABLE';Gerar o shrink:
select a.file_id,
a.file_name,
ceil((nvl(hwm,1)*8192)/1024) tamanho_para_diminuir,
ceil(blocks*8192/1024) tamanho_atual,
ceil(blocks*8192/1024) - ceil((nvl(hwm,1)*8192)/1024) espaco_livre,
'alter database datafile '||a.file_id||' resize '||ceil((nvl(hwm,1)*8192)/1024)||'k;'
from dba_data_files a,
(select file_id, max(block_id+blocks-1) hwm
from dba_extents where owner='TESTE'
group by file_id) b
where a.file_id = b.file_id;