Oracle reduzindo/recuperando espaço de tabela livre

Concordo com o comentário de EdStevens, mas aqui estão algumas outras ideias:

O site do AskTom tem um script que listará exatamente quanto você pode reduzir os arquivos de dados existentes.

Se isso não gerar muita economia, você poderá encontrar os maiores índices nesse tablespace e reconstruí-los; essa é uma solução temporária e os índices acabariam ficando grandes novamente, mas se o orçamento não permitir mais espaço em disco agora, tente esta consulta:

select segment_name, sum(bytes)/1024/1024 as mb 
from dba_segments 
where tablespace_name='TB1' and segment_type like 'INDEX%'
group by segment_name
order by mb;

Então, se você achar que um índice chamado ind_big_stuffé enorme, então

alter index ind_big_stuff rebuild online;

Além disso, se seu aplicativo for um aplicativo comercial, pode haver várias tabelas com zero linhas. Ou se você tiver tabelas particionadas, poderá ter algumas partições com zero linhas. Você pode soltar o espaço alocado a eles com

exec dbms_space_admin.drop_empty_segments(schema_name=>'HR');

Você pode usar a segment creation deferredcláusula em uma instrução alter table para coisas como tabelas particionadas ou tabelas que você sabe que não terão nenhuma linha.

Eu acho que existem cinco métodos para lidar com esse problema de manutenção do oráculo:

• Reorganização online - O pacote de reorganização de tabelas online do Oracle, (dbms_redefinition) é usado para reorganizar tabelas enquanto elas estão aceitando atualizações. Ele preserva as definições de índice e restrição. (Somente Enterprise Edition). Sem tempo de inatividade.
• Alter table shrink space - Usar o comando alter table shrink space compact irá reempacotar as linhas, mover para baixo o HWM e liberar extensões não utilizadas. A redução apenas compacta os dados. Não garante que os blocos serão colocados na frente do arquivo de dados, então é possível ter blocos no final do arquivo de dados, o que significa que o arquivo de dados não pode ser redimensionado. Ele preserva as definições de índice e restrição. Sem tempo de inatividade.
• Bomba de dados (expdp, impdp) – Método offline para transferir dados e reorganizar tabelas. Ele preserva as definições de índice e restrição. Tempo de inatividade necessário.
• Alter table move - O comando alter table move move as linhas para o espaço não utilizado e ajusta o HWM, mas não ajusta as extensões dos segmentos, e o tamanho da tabela permanece o mesmo. A sintaxe alter table move também preserva as definições de índice e restrição. Redimensionamento do arquivo de dados necessário. Tempo de inatividade necessário.
• Criar tabela como seleção (CTAS) - Isso copia as linhas da tabela em uma área limpa, diminuindo a marca d'água alta, empacotando as linhas densamente (conforme ditado pelo PCTFREE) e liberando espaço livre. Redimensionamento do arquivo de dados necessário. Tempo de inatividade necessário.

Procurei uma resposta para isso e finalmente encontrei:

• (opcionalmente) remova tabelas não utilizadas (descartadas) do tablespace:PURGE TABLESPACE table_space_name;
• limpe o tablespace:ALTER TABLESPACE table_space_name COALESCE;
• finalmente, redimensione os arquivos de dados:ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/file1.dbf' RESIZE 100M;

PS: Não tenho certeza sobre o tempo de inatividade, alguém por favor verifique isso.

PPS: Para tablespaces TEMP, use:

• ALTER TABLESPACE TEMP SHRINK SPACE ;(opcionalmente adicionar KEEP 512Mou similar)
• redimensione os dados/arquivos temporários:ALTER DATABASE TEMPFILE '/u02/oracle/rbdb1/temp01.dbf' RESIZE 512M;

Muito eficaz também é mover tudo para outro novo tablespace online:

## Lobs
select 'ALTER TABLE '||s.owner||'.'||l.table_name||' MOVE LOB('||l.column_name||') STORE AS (TABLESPACE USERNAME) online;' 
  from dba_segments s, dba_lobs l
 where s.segment_name = l.segment_name
   and s.tablespace_name = 'USERNAME'
   and segment_type='LOBSEGMENT'
   and partition_name is null;

## Tables move
select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'USERNAME online;' from dba_tables where tablespace_name='USERS'and Owner='USERNAME';


## Move / rebuild indexes 
select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'USERNAME_INDEX parallel 8 online;' 
from dba_indexes where tablespace_name='USERS';


## Lob Index move 
select 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as '||SEGMENT_NAME||' (tablespace USERNAME_INDEX);'from dba_lobs where OWNER='USERNAME' and tablespace_name='USERS';

##
Check Lob indexes
select index_name,index_type, table_name,table_type, tablespace_name from dba_indexes where tablespace_name='USERNAME' order by 3;
select index_name,index_type, table_name,table_type, tablespace_name from dba_indexes where owner='USERNAME' and index_type='LOB' and tablespace_name='USERS' order by 3;

Execute o script abaixo, ele liberará espaço não utilizado dos arquivos de dados do respectivo tablespace.

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

set pages 0
set lines 300
column cmd format a300 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
/

Verifique os detalhes em https://orahow.com/reclaiming-unused-space-in-datafiles/

https://orahow.com/reclaim-unused-space-in-oracle/ Tem 2 abordagens para identificar e recuperar espaço.