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 :
- É 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;
- É 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.
- 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
Parabéns
Muito bom
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!
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….
Blz Chiappa!
Muito obrigado pelo retorno detalhado, como de costume.
Abraço!