Pular para o conteúdo

Reutilizando espaço liberado por DELETEs numa tabela Oracle: Técnicas e demonstração

Como reutilizar o espaço liberado por DELETEs numa tabela

Uma dúvida recorrente para utilizadores do RDBMS ORACLE é como redesignar/reutilizar espaço sem uso numa tabela Oracle, neste artigo pretendo demonstrar algumas, que não envolvem recriação dos dados.

O Motivo para que essa situação exista é simples : por questão de performance, já que via de regra uma tabela é dinâmica (ie, os dados são constantemente inseridos, alterados, deletados e reinseridos), o RDBMS Oracle não marca como espaço livre o espaço liberado com a remoção dos dados : esse espaço é RESERVADO para a tabela que o usava, de modo que os próximos futuros INSERTs o reusarão…. NOTAR que esse espaço absolutamente NÃO está perdido, NÃO está fragmentado, ele VAI ser automagicamente reusado nos próximos INSERTs ou UPDATEs…

OK, mas e nos relativamente RAROS casos onde vc, usuário, tem a informação que o Oracle não tem, ie, de que uma determinada tabela NUNCA MAIS vai sofrer INSERTs, como desalocar (para usar alhures) esse espaço reservado para futuros INSERTs que não vão acontecer ? Veremos aqui…

ANTES de qualquer outra colocação, porém, há algumas observações e avisos importantes que precisam ser feitos :

  1. É dado aqui como sabido que a estrutura de armazenamento de dados no Oracle é : os registros/as linhas de uma tabela são gravados em blocos (uma estrutura em disco que pode abrigar várias linhas), cada alocação de espaço é feita não bloco a bloco mas sim num conjeunto de blocos contíguos chamado EXTENT , e o arquivo de dados (o datafile) vai ser dividido em diversos extents. O TAMANHO do bloco, a Quantidade de blocos agrupada em cada extent E o tamanhos dos datafiles são customizáveis pelo usuário, mas neste exemplo usarei o bloco default de 8 KB (8192 bytes), usarei EXTENTs gerenciados automaticamente pelo Oracle e a tablespace será composta por um só arquivo de 2 GB;
  2. É desnecessário dizer que se uma tabela chegou numa situação onde é Absolutamente Certo que os dados não serão nem inseridos nem atualizados nem removidos, é normalmente desejado que os dados ocupem o MENOR espaço possível – é mais que Certo então que as técnicas de Compressão de dados e índices seriam altamente indicadas aqui… Igualmente, por default em CADA bloco de dados é deixada uma margem reservada para possíveis UPDATEs (o chamado PCT_FREE), nessa situação onde é Certeza que nenhum UPDATE nem INSERT será feito, seria Indicado recriar o bloco SEM essa margem….AMBOS os itens acima não são contemplados pela técnica que será aqui descrita/demonstrada.
  3. Há algumas diferenças se a tablespace for configurada para Não Alocar o tamanho máximo desejado de uma vez mas sim ir extendendo o(s) datafile(s) inicialmente pequenos cfrme for necessário : neste exemplo criarei a tablespace já com o datafile no tamanho desejado, sem autoextensão… Colocações feitas, segue o exemplo/a demonstração…. Inicialmente, criaremos uma tablespace de 2 GB, com qtdade de blocos no extent E gerenciamento de espaço/freelists nos blocos controladas automaticamente, como é de uso comum, ao menos nas versões mais recentes do RDBMS :
container=ORCL12C:SYSTEM@ORCL12C> create tablespace TS_TESTE_SHRINK_DEALL datafile     'C:\APP\ORACLE12CR2 \ORADATA\ORCL12C\DATAFILE\TS_TESTE_SHRINK_DEALL_01.dbf' size 2G extent management local autoallocate Segment space management auto;

Tablespace criado.

=> Obviamente, como nenhum objeto foi criado ainda dentro dessa tablespace, NENHUM segmento físico e NENHUM conjunto de extents foi criado dentro dela :

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_segments where tablespace_name='TS_TESTE_SHRINK_DEALL'; 

não há linhas selecionadas

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_extents where tablespace_name='TS_TESTE_SHRINK_DEALL';

não há linhas selecionadas 

=> E o espaço para ela alocado tá totalmente livre :

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_free_space where tablespace_name='TS_TESTE_SHRINK_DEALL';

 TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
---------------------------------- -------- ---------- ------------ -------- ------------ 
 TS_TESTE_SHRINK_DEALL                   8        128 2146435072     262016            8 

 1 linha selecionada. 

 container=ORCL12C:SYSTEM@ORCL12C> 

=> Crio a tabela que será preenchida para termos massa de testes :

container=ORCL12C:SYSTEM@ORCL12C> create table TAB_TESTE_SHRINK_DEALL (c1 number, c2 varchar2(500) ) TABLESPACE TS_TESTE_SHRINK_DEALL;

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_extents where tablespace_name='TS_TESTE_SHRINK_DEALL';

 OWNER  SEGMENT_NAME            SEGMENT_TYPE TABLESPACE_NAME        EXTENT_ID FILE_ID   BLOCK_ID BYTES BLOCKS
------  ----------------------- ------------ ---------------------- --------- --------  -------- ----- -------   
 SYSTEM TAB_TESTE_SHRINK_DEALL  TABLE        TS_TESTE_SHRINK_DEALL          0       8        128 65536      8  

 1 linha selecionada. 

=> Veja acima que mesmo sem dados um EXTENT (vazio, evidemente) já foi criado para receber os futuros dados E esse extent foi criado a partir do bloco 128 : para tablespaces LMT há um overhead, um pequeno ‘cabeçalho’ que é usado para controle e gerenciamento que ocupa os blocos iniciais do datafile…

O restante do espaço continua presente no free space :

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_free_space where tablespace_name='TS_TESTE_SHRINK_DEALL';

 TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO 
---------------------------------- --------  -------- -----------    ------ ------------
 TS_TESTE_SHRINK_DEALL                   8        136 2146369536     262008            8 

 1 linha selecionada. 

=> Muito bem, vamos popular a tabela :

container=ORCL12C:SYSTEM@ORCL12C> BEGIN
       for i in 1..300000 loop
          insert into TAB_TESTE_SHRINK_DEALL values(SEQ_TESTE_SHRINK_DEALL.nextval, rpad('Linha=' || to_char(i, 'FM000000'),
    500, '*'));
       end loop;
       commit;
    END;
    /

 Procedimento PL/SQL concluído com sucesso. 

 container=ORCL12C:SYSTEM@ORCL12C> 

=> Eis como foi alocado o espaço : como esperado, o algoritmo de autocontrole da tablespace cria inicialmente alguns extents de 64 KB, depois cria alguns extents de 1 MB, assim por diante : esses números são cuidadosamente escolhidos por serem Múltiplos entre si, assim caso vários extents de 64 KB forem liberados eles podem ser posteriormente reunidos para formar um de 1 MB…

Vejamos :

container=ORCL12C:SYSTEM@ORCL12C> select segment_name, segment_type, tablespace_name, block_id, blocks, bytes 
from dba_extents where tablespace_name='TS_TESTE_SHRINK_DEALL'
 order by 1,2,3,4,5;

 SEGMENT_NAME                        SEGMENT_TYPE       TABLESPACE_NAME                  BLOCK_ID     BLOCKS      BYTES 
------------------------------------ ------------------ -------------------------------- ------------ ----------- ------
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 128          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 136          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 144          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 152          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 160          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 168          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 176          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 184          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 192          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 200          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 208          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 216          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 224          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 232          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 240          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 248          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 256        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 384        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 512        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 640        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 768        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 896        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1024        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1152        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1280        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1408        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1536        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1664        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1792        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1920        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2048        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2176        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2304        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2432        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2560        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2688        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2816        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2944        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3072        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3200        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3328        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3456        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3584        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3712        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3840        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3968        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4096        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4224        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4352        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4480        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4608        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4736        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4864        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4992        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5120        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5248        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5376        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5504        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5632        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5760        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5888        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6016        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6144        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6272        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6400        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6528        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6656        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6784        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6912        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7040        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7168        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7296        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7424        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7552        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7680        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7808        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7936        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8064        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8192        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8320       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                9344       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               10368       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               11392       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               12416       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               13440       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               14464       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               15488       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               16512       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               17536       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               18560       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               19584       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               20608       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               21632       1024    8388608

 93 linhas selecionadas.  
 
 container=ORCL12C:SYSTEM@ORCL12C> 

=> ok… Nesta versão (12cR2) que usei, o default é que a tabela seja criada SEM ROW MOVEMENT e com PCT_FREE de 10%:

container=ORCL12C:SYSTEM@ORCL12C> select row_movement, STATUS,PCT_FREE, PCT_USED,PCT_INCREASE, initial_extent
  from user_tables 
where table_name='TAB_TESTE_SHRINK_DEALL';

 ROW_MOVE STATUS     PCT_FREE   PCT_USED PCT_INCREASE INITIAL_EXTENT 
 ------------------- ---------  -------- ---------------------------
 DISABLED VALID            10                                  65536 

 1 linha selecionada. 

 container=ORCL12C:SYSTEM@ORCL12C> 

=> Demonstrando, as linhas da tabela foram sendo inseridas cfrme os blocos foram sendo encontrados, E em um bloco mais de uma linha cabe :

container=ORCL12C:SYSTEM@ORCL12C> select * from (select c1, rowid, dbms_rowid.rowid_block_number(rowid)  blocknum from TAB_TESTE_SHRINK_DEALL)
  where blocknum < 137 order by blocknum;

     C1 ROWID                BLOCKNUM 
    --- -------------------- --------
    30 AAASexAAIAAAACDAAB        131
    29 AAASexAAIAAAACDAAA        131
    31 AAASexAAIAAAACDAAC        131
    32 AAASexAAIAAAACDAAD        131
    33 AAASexAAIAAAACDAAE        131
    34 AAASexAAIAAAACDAAF        131
    42 AAASexAAIAAAACDAAN        131
    36 AAASexAAIAAAACDAAH        131
    37 AAASexAAIAAAACDAAI        131
    38 AAASexAAIAAAACDAAJ        131
    39 AAASexAAIAAAACDAAK        131
    40 AAASexAAIAAAACDAAL        131
    41 AAASexAAIAAAACDAAM        131
    35 AAASexAAIAAAACDAAG        131
    44 AAASexAAIAAAACEAAB        132
    43 AAASexAAIAAAACEAAA        132
    45 AAASexAAIAAAACEAAC        132
    56 AAASexAAIAAAACEAAN        132
    55 AAASexAAIAAAACEAAM        132
    57 AAASexAAIAAAACFAAA        133
    58 AAASexAAIAAAACFAAB        133
    59 AAASexAAIAAAACFAAC        133
    60 AAASexAAIAAAACFAAD        133
    61 AAASexAAIAAAACFAAE        133
    62 AAASexAAIAAAACFAAF        133
    70 AAASexAAIAAAACFAAN        133
    64 AAASexAAIAAAACFAAH        133
    65 AAASexAAIAAAACFAAI        133
    66 AAASexAAIAAAACFAAJ        133
    67 AAASexAAIAAAACFAAK        133
    69 AAASexAAIAAAACFAAM        133
    68 AAASexAAIAAAACFAAL        133
    63 AAASexAAIAAAACFAAG        133
     1 AAASexAAIAAAACGAAA        134
     2 AAASexAAIAAAACGAAB        134
     3 AAASexAAIAAAACGAAC        134
     4 AAASexAAIAAAACGAAD        134
     5 AAASexAAIAAAACGAAE        134
     6 AAASexAAIAAAACGAAF        134
     7 AAASexAAIAAAACGAAG        134
     8 AAASexAAIAAAACGAAH        134
     9 AAASexAAIAAAACGAAI        134
    10 AAASexAAIAAAACGAAJ        134
    11 AAASexAAIAAAACGAAK        134
    12 AAASexAAIAAAACGAAL        134
    13 AAASexAAIAAAACGAAM        134
    14 AAASexAAIAAAACGAAN        134
    15 AAASexAAIAAAACHAAA        135
    16 AAASexAAIAAAACHAAB        135
    28 AAASexAAIAAAACHAAN        135
    27 AAASexAAIAAAACHAAM        135
    26 AAASexAAIAAAACHAAL        135
    25 AAASexAAIAAAACHAAK        135
    24 AAASexAAIAAAACHAAJ        135
    23 AAASexAAIAAAACHAAI        135
    22 AAASexAAIAAAACHAAH        135
    21 AAASexAAIAAAACHAAG        135
    20 AAASexAAIAAAACHAAF        135
    19 AAASexAAIAAAACHAAE        135
    18 AAASexAAIAAAACHAAD        135
    17 AAASexAAIAAAACHAAC        135
    85 AAASexAAIAAAACIAAA        136
    97 AAASexAAIAAAACIAAM        136
    96 AAASexAAIAAAACIAAL        136
    95 AAASexAAIAAAACIAAK        136
    94 AAASexAAIAAAACIAAJ        136
    93 AAASexAAIAAAACIAAI        136
    98 AAASexAAIAAAACIAAN        136
    91 AAASexAAIAAAACIAAG        136
    90 AAASexAAIAAAACIAAF        136
    89 AAASexAAIAAAACIAAE        136
    88 AAASexAAIAAAACIAAD        136
    87 AAASexAAIAAAACIAAC        136
    86 AAASexAAIAAAACIAAB        136
    92 AAASexAAIAAAACIAAH        136 

 75 linhas selecionadas. 

 container=ORCL12C:SYSTEM@ORCL12C> 

==> Muito bem : para que eu possa demonstrar a técnica de realocação de espaço que pretendo demonstrar (que será a combinação de SHRINK+DEALLOCATE) antes de mais nada tenho que Habilitar o ROW MOVEMENT :

container=ORCL12C:SYSTEM@ORCL12C> alter table TAB_TESTE_SHRINK_DEALL enable row movement;

Tabela alterada.

container=ORCL12C:SYSTEM@ORCL12C> alter table TAB_TESTE_SHRINK_DEALL shrink space;

Tabela alterada.

container=ORCL12C:SYSTEM@ORCL12C> select * from (select c1, rowid, dbms_rowid.rowid_block_number(rowid)  blocknum from TAB_TESTE_SHRINK_DEALL)
   where blocknum < 137 order by blocknum;

     C1 ROWID                BLOCKNUM 
    --- -------------------- --------
    34 AAASexAAIAAAACDAAF        131
    33 AAASexAAIAAAACDAAE        131
    32 AAASexAAIAAAACDAAD        131  
    31 AAASexAAIAAAACDAAC        131
    30 AAASexAAIAAAACDAAB        131
    29 AAASexAAIAAAACDAAA        131
    42 AAASexAAIAAAACDAAN        131
    36 AAASexAAIAAAACDAAH        131
    37 AAASexAAIAAAACDAAI        131
    38 AAASexAAIAAAACDAAJ        131
    39 AAASexAAIAAAACDAAK        131
    40 AAASexAAIAAAACDAAL        131
    41 AAASexAAIAAAACDAAM        131
    35 AAASexAAIAAAACDAAG        131
298609 AAASexAAIAAAACEAAF        132
298608 AAASexAAIAAAACEAAE        132
298607 AAASexAAIAAAACEAAD        132
    45 AAASexAAIAAAACEAAC        132
    44 AAASexAAIAAAACEAAB        132
    43 AAASexAAIAAAACEAAA        132
298610 AAASexAAIAAAACEAAG        132
    56 AAASexAAIAAAACEAAN        132
    55 AAASexAAIAAAACEAAM        132
298615 AAASexAAIAAAACEAAL        132
298614 AAASexAAIAAAACEAAK        132
298613 AAASexAAIAAAACEAAJ        132
298612 AAASexAAIAAAACEAAI        132
298611 AAASexAAIAAAACEAAH        132
    57 AAASexAAIAAAACFAAA        133
    58 AAASexAAIAAAACFAAB        133
    59 AAASexAAIAAAACFAAC        133
    60 AAASexAAIAAAACFAAD        133
    61 AAASexAAIAAAACFAAE        133
    62 AAASexAAIAAAACFAAF        133
    70 AAASexAAIAAAACFAAN        133
    68 AAASexAAIAAAACFAAL        133
    67 AAASexAAIAAAACFAAK        133
    66 AAASexAAIAAAACFAAJ        133
    65 AAASexAAIAAAACFAAI        133
    64 AAASexAAIAAAACFAAH        133
    63 AAASexAAIAAAACFAAG        133
    69 AAASexAAIAAAACFAAM        133
     1 AAASexAAIAAAACGAAA        134
     2 AAASexAAIAAAACGAAB        134
     3 AAASexAAIAAAACGAAC        134
     4 AAASexAAIAAAACGAAD        134
     5 AAASexAAIAAAACGAAE        134
     6 AAASexAAIAAAACGAAF        134
     7 AAASexAAIAAAACGAAG        134
     8 AAASexAAIAAAACGAAH        134
     9 AAASexAAIAAAACGAAI        134
    10 AAASexAAIAAAACGAAJ        134
    11 AAASexAAIAAAACGAAK        134
    12 AAASexAAIAAAACGAAL        134
    13 AAASexAAIAAAACGAAM        134
    14 AAASexAAIAAAACGAAN        134
    15 AAASexAAIAAAACHAAA        135
    16 AAASexAAIAAAACHAAB        135
    28 AAASexAAIAAAACHAAN        135
    27 AAASexAAIAAAACHAAM        135
    26 AAASexAAIAAAACHAAL        135
    25 AAASexAAIAAAACHAAK        135
    24 AAASexAAIAAAACHAAJ        135
    23 AAASexAAIAAAACHAAI        135
    22 AAASexAAIAAAACHAAH        135
    21 AAASexAAIAAAACHAAG        135
    20 AAASexAAIAAAACHAAF        135
    19 AAASexAAIAAAACHAAE        135
    18 AAASexAAIAAAACHAAD        135
    17 AAASexAAIAAAACHAAC        135
    85 AAASexAAIAAAACIAAA        136
    97 AAASexAAIAAAACIAAM        136
    96 AAASexAAIAAAACIAAL        136
    95 AAASexAAIAAAACIAAK        136
    94 AAASexAAIAAAACIAAJ        136
    93 AAASexAAIAAAACIAAI        136
    98 AAASexAAIAAAACIAAN        136
    91 AAASexAAIAAAACIAAG        136
    90 AAASexAAIAAAACIAAF        136
    89 AAASexAAIAAAACIAAE        136
    88 AAASexAAIAAAACIAAD        136
    87 AAASexAAIAAAACIAAC        136
    86 AAASexAAIAAAACIAAB        136
    92 AAASexAAIAAAACIAAH        136 

 84 linhas selecionadas. 

 container=ORCL12C:SYSTEM@ORCL12C> 

==> Agora vamos simular a situação de liberar MUITO espaço reservado após o DELETE : primeiro, vamos fazer alguns DELETEs massivos, que vai liberar Vários Blocos Inteiros – é indiferente fazer DELETEs pequenos repetidas vezes ou um único DELETE com muitas linhas…
Eis o comando :

container=ORCL12C:SYSTEM@ORCL12C> delete from TAB_TESTE_SHRINK_DEALL where dbms_rowid.rowid_block_number(rowid)
   between 2048 and 4096;

28224 linhas excluídas.

container=ORCL12C:SYSTEM@ORCL12C> delete from TAB_TESTE_SHRINK_DEALL where dbms_rowid.rowid_block_number(rowid)
   between 11392 and 14464;

42840 linhas excluídas.

container=ORCL12C:SYSTEM@ORCL12C> commit;

Commit concluído.

==> Eis como ficou o espaço alocado :

container=ORCL12C:SYSTEM@ORCL12C> select segment_name, segment_type, tablespace_name, block_id, blocks, bytes 
from dba_extents where tablespace_name='TS_TESTE_SHRINK_DEALL';

SEGMENT_NAME                        SEGMENT_TYPE       TABLESPACE_NAME                  BLOCK_ID     BLOCKS      BYTES
----------------------------------- ------------------ -------------------------------- ------------ -------- ----------
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 128          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 136          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 144          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 152          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 160          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 168          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 176          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 184          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 192          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 200          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 208          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 216          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 224          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 232          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 240          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 248          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 256        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 384        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 512        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 640        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 768        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 896        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1024        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1152        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1280        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1408        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1536        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1664        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1792        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1920        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2048        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2176        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2304        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2432        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2560        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2688        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2816        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2944        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3072        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3200        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3328        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3456        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3584        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3712        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3840        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3968        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4096        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4224        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4352        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4480        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4608        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4736        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4864        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4992        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5120        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5248        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5376        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5504        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5632        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5760        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5888        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6016        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6144        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6272        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6400        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6528        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6656        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6784        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6912        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7040        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7168        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7296        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7424        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7552        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7680        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7808        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7936        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8064        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8192        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8320       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                9344       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               10368       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               11392       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               12416       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               13440       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               14464       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               15488       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               16512       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               17536       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               18560       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               19584       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               20608       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               21632        120     983040

93 linhas selecionadas.

==> Ficou EXATAMENTE O MESMO!!! ESSE é o ponto que queríamos demonstrar inicialmente : mesmo após um DELETE esvaziar completamente um ou mais blocos, esses blocos CONTINUAM alocados nos extents originais E esses extents continuam marcados como em uso para o segmento fisico , MESMO a quantidade de linhas (e portanto de dados ter diminuido sensivelmente), visando Justamente que os futuros INSERTs ocupem esses espaços em branco :

container=ORCL12C:SYSTEM@ORCL12C> select count(*) from TAB_TESTE_SHRINK_DEALL;

COUNT(*)
--------
228935

1 linha selecionada.

container=ORCL12C:SYSTEM@ORCL12C>

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_free_space where tablespace_name='TS_TESTE_SHRINK_DEALL';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
--------------------------------- --------  --------- ----------    ------ -------------
TS_TESTE_SHRINK_DEALL                   8      21752 1969291264     240392            8

1 linha selecionada.

container=ORCL12C:SYSTEM@ORCL12C>

==> Agora primeiro vamos mover as LINHAS de dados para que ocupem os slots vagos desde o primeiro bloco :

container=ORCL12C:SYSTEM@ORCL12C> alter table TAB_TESTE_SHRINK_DEALL shrink space;

Tabela alterada.

=> Esse comando acima transferiu as linhas todas para os slots vazios, e assim deixou o espaço em branco no final da tabela e agora posso desalocá-lo :

container=ORCL12C:SYSTEM@ORCL12C> alter table TAB_TESTE_SHRINK_DEALL deallocate unused;

Tabela alterada.

=> Pronto, agora sim o os extents foram redefinidos :

container=ORCL12C:SYSTEM@ORCL12C> select segment_name, segment_type, tablespace_name, block_id, blocks, bytes 
from dba_extents where tablespace_name='TS_TESTE_SHRINK_DEALL';

SEGMENT_NAME                        SEGMENT_TYPE       TABLESPACE_NAME                  BLOCK_ID     BLOCKS      BYTES
----------------------------------- -----------------  -------------------------------- ----------- --------- ---------
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 128          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 136          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 144          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 152          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 160          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 168          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 176          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 184          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 192          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 200          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 208          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 216          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 224          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 232          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 240          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 248          8      65536
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 256        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 384        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 512        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 640        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 768        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                 896        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1024        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1152        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1280        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1408        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1536        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1664        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1792        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                1920        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2048        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2176        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2304        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2432        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2560        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2688        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2816        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                2944        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3072        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3200        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3328        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3456        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3584        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3712        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3840        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                3968        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4096        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4224        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4352        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4480        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4608        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4736        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4864        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                4992        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5120        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5248        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5376        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5504        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5632        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5760        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                5888        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6016        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6144        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6272        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6400        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6528        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6656        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6784        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                6912        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7040        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7168        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7296        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7424        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7552        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7680        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7808        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                7936        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8064        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8192        128    1048576
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                8320       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL                9344       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               10368       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               11392       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               12416       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               13440       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               14464       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               15488       1024    8388608
 TAB_TESTE_SHRINK_DEALL              TABLE              TS_TESTE_SHRINK_DEALL               16512        144    1179648

88 linhas selecionadas.

container=ORCL12C:SYSTEM@ORCL12C> 

==> Pronto, a tabela que antes ocupava do bloco 8 do datafile até o bloco 21632+120=21752 passou a ir só até o bloco 16512+144=16656 , E o espaço liberado pelo DELETE foi DESALOCADO, indo para a lista de espaço livre na tablespace, disponível para qualquer objeto que precisar : por isso, o espaço livre depois do DELETE que era de 1969291264 bytes passou para 2011037696 após o SHRINK+DEALLOCATE, conforme :

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_free_space where tablespace_name='TS_TESTE_SHRINK_DEALL';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
--------------------------------- -------- ---------- -----------  ------- ------------
TS_TESTE_SHRINK_DEALL                   8      16656 2011037696     245488            8

1 linha selecionada.

container=ORCL12C:SYSTEM@ORCL12C>

Ok ??

EXTRA : obviamente, uma vez alocado para um datafile qualquer, o espaço Daí não sai nunca mais, e flutua entre a FREE SPACE e a DBA_EXTENTS/DBA_SEGMENTS…. SE for desejado realocar esse espaço de volta para o Sistema Operacional, de modo que ele possa ser usado em Outros datafiles, vc deve Manualmente encolher o datafile… No meu exemplo, o datafile foi criado com 2 GB :

container=ORCL12C:SYSTEM@ORCL12C> host dir C:\APP\ORACLE12CR2\ORADATA\ORCL12C\DATAFILE\TS_TESTE_SHRINK_DEALL_01.dbf
  O volume na unidade C não tem nome.
  O Número de Série do Volume é 340C-E794

Pasta de C:\APP\ORACLE12CR2\ORADATA\ORCL12C\DATAFILE

17/12/2019  10:51     2.147.491.840 TS_TESTE_SHRINK_DEALL_01.DBF
                1 arquivo(s)  2.147.491.840 bytes
                0 pasta(s)   113.671.049.216 bytes disponíveis

container=ORCL12C:SYSTEM@ORCL12C>

==> Se for desejado encolher ele para apenas o minimo que contém dados, seria assim :

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_free_space 
where tablespace_name='TS_TESTE_SHRINK_DEALL';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
---------------------------------- ------- ---------- ---------- --------- ------------
TS_TESTE_SHRINK_DEALL                   8      16656 2011037696     245488            8

1 linha selecionada.

container=ORCL12C:SYSTEM@ORCL12C> host dir C:\APP\ORACLE12CR2\ORADATA\ORCL12C\DATAFILE\TS_TESTE_SHRINK_DEALL_01.dbf
  O volume na unidade C não tem nome.
  O Número de Série do Volume é 340C-E794

Pasta de C:\APP\ORACLE12CR2\ORADATA\ORCL12C\DATAFILE

17/12/2019  10:51     2.147.491.840 TS_TESTE_SHRINK_DEALL_01.DBF
                1 arquivo(s)  2.147.491.840 bytes
                0 pasta(s)   113.671.049.216 bytes disponíveis

container=ORCL12C:SYSTEM@ORCL12C> select 16512+144 qtd_blocos from dual;

QTD_BLOCOS
----------
 16656

1 linha selecionada.

=> Sabendo quantos blocos estão ocupados E que não há grandes espaços em branco entre os extents E o tamanho do bloco, posso calcular até onde o datafile pode ser diminuído :

container=ORCL12C:SYSTEM@ORCL12C> select 16656 * 8192 from dual;

16656*8192
-----------
136445952

1 linha selecionada.

container=ORCL12C:SYSTEM@ORCL12C> alter database datafile 'C:\APP\ORACLE12CR2\ORADATA\ORCL12C\DATAFILE\TS_TESTE_SHRINK_DEALL_01.dbf' resize 136445952;

Banco de dados alterado.

=> Assim ficou o espaço em disco :

container=ORCL12C:SYSTEM@ORCL12C> host dir C:\APP\ORACLE12CR2\ORADATA\ORCL12C\DATAFILE\TS_TESTE_SHRINK_DEALL_01.dbf
  O volume na unidade C não tem nome.
  O Número de Série do Volume é 340C-E794

Pasta de C:\APP\ORACLE12CR2\ORADATA\ORCL12C\DATAFILE

17/12/2019  18:12       136.454.144 TS_TESTE_SHRINK_DEALL_01.DBF
                1 arquivo(s)    136.454.144 bytes
                0 pasta(s)   115.681.341.440 bytes disponíveis

container=ORCL12C:SYSTEM@ORCL12C>

=> O espaço FOI devolvido para o Sistema Operacional com a diminuição do datafile :

container=ORCL12C:SYSTEM@ORCL12C> select * from dba_free_space 
where tablespace_name='TS_TESTE_SHRINK_DEALL';

não há linhas selecionadas

container=ORCL12C:SYSTEM@ORCL12C>

Abraços

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 33

Sem votos ! Seja o primeiro a classificar !

5 comentários em “Reutilizando espaço liberado por DELETEs numa tabela Oracle: Técnicas e demonstração”

  1. Avatar de MarcioRM

    Muito bom!
    Quais implicações teríamos utilizando o “alter table TABLE move”? Tenho utilizado este comando para reclamar o espaço utilizado na tablespace após realizar uma limpeza de dados antigos/históricos.
    Sei que índices da tabela em questão precisam ser recriados, há algo mais que pode justificar a não utilização do “move”?
    Obrigado!

  2. Avatar de José Laurindo Chiappa

    Blz, Márcio ? Afora a questão de performance (o tempo que pode levar a operação numa tabela grande) e eventuais necessidades de recriação de índices, há OUTROS pontos que podem inviabilizar um MOVE , dependendo se vc vai fazer a operação ONLINE ou não, da versão de banco, etc… De modo geral :
    – o MOVE, é claro, vai colocar um lock de DDL na tabela, então Outros DDLs não vão ser possíveis enquanto o MOVE tá rolando, EM ESPECIAL se vc for Paralelizar a operação
    – vc não pode usar isso para tabelas que contém DOMAIN INDEXES
    – podem haver restrições para alguns tipos de Particionamento
    – podem haver restrições para tabelas que contenham colunas de datatypes não-escalares (como LOBs ou XML ou JSON) e/ou colunas VARRAY e coisas do tipo

    Mas para uma tabela com datatypes Escalares, não particionada, sem índices especializados E que vc tenha um horário de manutenção (pra evitar issues de concorrência/aguardo por lock) E que não seja de tamanho /volume gigantesco, não vejo nenhum grande impedimento, não….

Deixe um comentário para MarcioRM Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress