Recuperação de espaço em disco livre de arquivo de dados simplificado/automatizado

A resposta curta é Não . Infelizmente, a maneira de fazer isso no Oracle requer a "grande pilha de consultas chatas". Os artigos aos quais você vinculou são algumas das melhores informações disponíveis sobre o assunto. O arquivo de dados realmente se torna fragmentado, de modo que, mesmo que exista espaço livre abaixo do segmento mais alto, o Oracle não o consolidará automaticamente quando a RESIZEfor concluído.

Para "desfragmentar" o tablespace, você precisa mover esses segmentos para o início do arquivo de dados e não para o final. Para mesas, este é um processo offline, o que significa que a mesa não estará disponível enquanto a movimentação estiver ocorrendo. Os índices podem ser movidos offline ou com Enterprise Edition eles podem ser movidos online. Como você tem uma janela de interrupção, recomendo que você siga estas etapas.

A. Reduza os arquivos de dados com espaço livre além do limite máximo. Isso pode ser feito da seguinte forma (a consulta é semelhante ao procedimento do Frosty Z):

SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
   ceil( blocks*(a.BlockSize)/1024/1024) -
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
   'alter database datafile '''|| file_name || ''' resize ' || 
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a 
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) 
   > 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;

B. Depois de reduzir as coisas acima da marca d'água, descubra quais tablespaces ainda se beneficiariam com a movimentação de segmentos.

SELECT DISTINCT tablespace_name FROM
(      
    SELECT tablespace_name, block_id + blocks LastBlock,
       lead(block_id) OVER (PARTITION BY File_ID 
          ORDER BY tablespace_name, file_id, block_id) NextBlock
       FROM dba_free_space 
) WHERE LastBlock <> NextBlock AND NextBlock IS NOT NULL;

C. Para cada um desses tablespaces, determine quais segmentos precisam ser movidos. (Substitua USERS pelo nome do seu tablespace ou una-o com a consulta anterior)

SELECT distinct de.segment_name
FROM dba_extents de
JOIN
(
   SELECT tablespace_name, file_id, MIN(block_id) LowestFreeBlock
   FROM dba_free_space
   WHERE tablespace_name = 'USERS'
  GROUP BY tablespace_name, file_id
) dfs ON dfs.tablespace_name = de.tablespace_name AND dfs.file_id = de.file_id
WHERE de.tablespace_name = 'USERS'
AND de.block_id > dfs.LowestFreeBlock;

D. Mova cada tabela e reconstrua os índices e estatísticas.

E. Repita a etapa A.

Acabei de construir a maioria dessas consultas, então você vai querer testá-las completamente antes de usar. Suponho que você poderia criar um procedimento que usaria EXECUTE IMMEDIATEpara criar as instruções reais para executar dinamicamente, mas como as consultas receberão ORA-08103: O objeto não existe mais enquanto a movimentação está em andamento, acho melhor controlar esse processo manualmente mesmo se isso significar um pouco mais de tempo/esforço.

Solução parcial inspirada nesta página :

Ele não reorganiza o espaço livre, mas detecta automaticamente o espaço livre disponível no final dos arquivos de dados e imprime os comandos 'RESIZE' apropriados.

DECLARE
    BLKSIZE INTEGER;

BEGIN
    SELECT VALUE INTO BLKSIZE FROM V$PARAMETER WHERE NAME = 'db_block_size';

    FOR INDEX_ROW IN (
      SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*BLKSIZE)/1024/1024 ) || 'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
            (SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
            WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* BLKSIZE)/1024/1024 ) > 0
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(INDEX_ROW.SHRINK_DATAFILES);
    END LOOP;
END;

Antes de tentar reduzir os arquivos de dados, pergunte a si mesmo: você vai criar novos segmentos novamente dentro do espaço de tabela associado em algum momento em um futuro não tão distante? Se sim, não adianta diminuir. O espaço será reutilizado para seus novos segmentos e você economiza muito esforço para si mesmo e para o sistema, deixando-o como está.

Depois de navegar no google por dias, encontrei o exemplo mais simples e claro para recuperar o espaço livre no tablespace após a exclusão. Eu espero que isso ajude

Link: http://www.dbforums.com/oracle/976248-how-reduce-tablespaces-used-space-after-delete-records-2.html

solução:

ALTER TABLE MOVE demo

Vamos criar uma tabela com 9999 linhas, cada uma com cerca de 1k:

SQL> create table t (x char(1000) default 'x' primary key);
Table created.
SQL> insert /*+ append nologging */ into t(x) select rownum from all_objects where rownum < 10000;
9999 rows created.
SQL> commit;
Commit complete.

A tabela tem 29 extensões alocadas a ela, para um total de 14,6M:

SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
29 14680064

Vamos deletar TODAS as linhas:

SQL> delete from t;
9999 rows deleted.
SQL> commit;
Commit complete.

Agora- "surpresa" - a tabela ainda usa as mesmas extensões:

SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
29 14680064

Por quê ? Porque mesmo que você exclua todas as linhas da tabela, o High Water Mark não é diminuído - ele nunca é diminuído, para permitir a simultaneidade máxima (Oracle leva a sério a maximização da simultaneidade, ou seja, desempenho e escalabilidade; é a principal razão por trás de seu sucesso em aplicativos corporativos).

Desalocar o espaço não utilizado (= espaço acima do HWM) não ajuda muito (já que não há muito espaço não utilizado acima do HWM):

SQL> alter table t deallocate unused;
Table altered.
SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
29 13959168

Agora, vamos MOVER a tabela, que em essência significa clonar a tabela (incluindo gatilhos, restrições e assim por diante), transferir as linhas, descartar a tabela "antiga" e renomear a nova - tudo feito pelo kernel, tão super seguro mesmo em caso de falha da máquina/servidor:

SQL> alter table t move;
Table altered.

Agora, temos agora apenas a extensão inicial alocada:

SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
1 65536

Advertência: normalmente acontece que muitos/todos os índices na tabela ficam INUSÁVEIS após a mudança (não neste caso, mas estou executando a versão 9.2.0.4, a versão mais recente, que provavelmente otimizou o processo no caso de tabelas totalmente vazias ):

SQL> col table_name form a30
SQL> col index_name form a30
SQL> set lines 123 
SQL> select table_name, index_name, status from user_indexes where table_name='T';

TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ ------------------------
T SYS_C002573 VALID

Se STATUS não fosse VALID, você poderia simplesmente reconstruir manualmente o(s) índice(s):

SQL> alter index SYS_C002573 rebuild;
Index altered.

Ou você pode automatizar todo o processo:

set serveroutput on size 100000
begin
for n in (select index_name from user_indexes where status <> 'VALID') loop
dbms_output.put_line ('rebuilding ' || n.index_name);
execute immediate 'alter index ' || n.index_name || ' rebuild';
end loop;
end;
/

Como exemplo, vamos definir manualmente o índice para UNUSABLE:

SQL> alter index SYS_C002573 unusable;
Index altered.

SQL> set serveroutput on size 100000
SQL> begin
2 for n in (select index_name from user_indexes where status <> 'VALID') loop
3 dbms_output.put_line ('rebuilding ' || n.index_name);
4 execute immediate 'alter index ' || n.index_name || ' rebuild';
5 end loop;
6 end;
7 /
rebuilding SYS_C002573

PL/SQL procedure successfully completed.

HTH Alberto

Como dito anteriormente, você terá que mover todas as mais de 200 tabelas nesse tablespace para liberar algum espaço em seu arquivo de dados e, em seguida, redimensionar para recuperar o espaço. Mas em vez de executar todas essas consultas, o 12c Enterprise Manager faz essa tarefa. Você terá que navegar para Database Home > Storage > Tablespace. Selecione o tablespace no qual deseja trabalhar e clique em Reorganizar. Ele dará uma opção para visualizar as instruções SQL que estão prestes a ser executadas. Você pode fazer uma cópia deles e executá-lo você mesmo ou agendar um trabalho no EM.

Na verdade, ele cria outro tablespace, move todos os objetos para o novo tablespace, reconstrói os índices e remove os objetos do tablespace antigo.

Há um par de desvantagens que posso pensar. Isso deve ser feito fora do horário de pico, caso contrário, ocorrerá um erro dizendo que o recurso está ocupado. O arquivo de dados (não o tablespace) terá "reorg" adicionado ao seu nome, no final.