Olá,
Para realizarmos a operação de SHRINK em um segmento LOB é necessário obrigatoriamente que a tablespace onde reside o segmento seja do tipo ASSM (Automatic Segment Space Management). Vale a pena salientar que no Oracle 10g R1 quando executamos o comando SHRINK com a opção CASCADE apenas os índices são incluídos na operação, os LOBS não. Já a partir do Oracle 10g R2 os segmentos de LOB são incluídos na operação de SHRINK.
Portanto, existem 2 formas de realizar a operação de SHRINK em um segmento LOB conforme abaixo:
- ALTER TABLE [table_name] MODIFY LOB ([lob_column]) (SHRINK SPACE);
- ALTER TABLE [table_name] SHRINK SPACE CASCADE;
Na simulação a seguir irei demonstrar esta operação de SHRINK em um tabela (T1) que possui uma coluna (IMAGEM) do tipo BLOB.
SQL> desc t1;
Nome Nulo? Tipo
----------------------- -------- ----------------
ID NUMBER
IMAGEM BLOB
Podemos observar abaixo que a tabela possui atualmente 372,06 MB de dados em uso.
SQL> SELECT a.tablespace_name,
2 trunc(b.used_percent,2) used_percent,
3 trunc(b.used_space * a.block_size/1024/1024,2) used_space_MB,
4 c.free_mb,trunc(b.tablespace_size * a.block_size/1024/1024,2) tablespace_max_size_MB
5 FROM dba_tablespaces a,
6 dba_tablespace_usage_metrics b,
7 ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_mb
8 FROM dba_free_space
9 GROUP BY tablespace_name) c
10 WHERE a.tablespace_name = b.tablespace_name
11 AND b.tablespace_name = c.tablespace_name
12 AND a.contents = 'PERMANENT'
13 AND a.tablespace_name = 'TBS_DATA';
TABLESPACE_NAME USED_PERCENT USED_SPACE_MB FREE_MB TABLESPACE_MAX_SIZE_MB
------------------------- ------------ ------------- ---------- ----------------------
TBS_DATA 72,29 372,06 142,5625 514,62
1 linha selecionada.
Em relação aos dados da tabela, podemos perceber abaixo que dos 372,06 MB usados, 355,92 MB estão sendo utilizados pelo segmento de LOB (coluna IMAGEM).
SQL> SELECT SPACE_ALLOCATED_MB,
2 SPACE_USED_MB,
3 SPACE_ALLOCATED_MB - SPACE_USED_MB SPACE_RECLAIMABLE_MB
4 FROM
5 (
6 SELECT
7 (SELECT round(s.bytes/1024/1024,2) MB FROM dba_segments s JOIN dba_lobs l USING (owner, segment_name)
8 WHERE l.table_name = 'T1' AND OWNER='SCOTT') SPACE_ALLOCATED_MB,
9 (SELECT round(nvl((sum(dbms_lob.getlength(IMAGEM))),0)/1024/1024,2) MB FROM SCOTT.T1) SPACE_USED_MB
10 FROM DUAL);
SPACE_ALLOCATED_MB SPACE_USED_MB SPACE_RECLAIMABLE_MB
------------------ ------------- --------------------
368,94 355,92 13,02
1 linha selecionada.
Veremos agora o que acontece se removermos uma parte considerável de registros da tabela.
SQL> delete from t1 where id between 1 and 99567;
99567 linhas deletadas.
SQL> commit;
Commit concluído.
Após a remoção dos registros, podemos perceber que essa ação não liberou o espaço removido de volta para a tablespace, ou seja, os 372,06 MB continuam alocados para o segmento LOB para reutilização.
SQL> SELECT a.tablespace_name,
2 trunc(b.used_percent,2) used_percent,
3 trunc(b.used_space * a.block_size/1024/1024,2) used_space_MB,
4 c.free_mb,trunc(b.tablespace_size * a.block_size/1024/1024,2) tablespace_max_size_MB
5 FROM dba_tablespaces a,
6 dba_tablespace_usage_metrics b,
7 ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_mb
8 FROM dba_free_space
9 GROUP BY tablespace_name) c
10 WHERE a.tablespace_name = b.tablespace_name
11 AND b.tablespace_name = c.tablespace_name
12 AND a.contents = 'PERMANENT'
13 AND a.tablespace_name = 'TBS_DATA';
TABLESPACE_NAME USED_PERCENT USED_SPACE_MB FREE_MB TABLESPACE_MAX_SIZE_MB
------------------------- ------------ ------------- ---------- ----------------------
TBS_DATA 72,29 372,06 142,5625 514,62
1 linha selecionada.
Para que o espaço possa ser reaproveitado para outros objetos na tablespace, deveremos realizar o SHRINK do segmento LOB conforme a seguir. Ao contrário de mover o segmento (ALTER TABLE ... MOVE LOB), o comando SHRINK não deixa os índice da tabela inutilizável e neste caso não é necessário realizar um "rebuild" nos índices da tabela. Vale a pena salientar que para realizar o SHRINK é necessário habilitar o ROW MOVEMENT na tabela. Abaixo, irei realizar o SHRINK em duas etapas. A opção COMPACT irá liberar o espaço na tablespace sem prejudicar as operações DML porventura executadas na tabela durante a operação, ou seja, ele pode ser realizada de forma ONLINE. O próximo comando, sem a opção COMPACT, irá efetivamente ajustar a High Water Mark (HWM) da tabela e, neste caso, um lock exclusivo será imposto na tabela o que irá bloquear qualquer operação DML na mesma até a finalização da operação.
SQL> alter table t1 shrink space cascade;
alter table t1 shrink space cascade
*
ERRO na linha 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> alter table t1 enable row movement;
Tabela alterada.
SQL> alter table t1 shrink space compact cascade;
Tabela alterada.
SQL> alter table t1 shrink space cascade;
Tabela alterada.
Após alguns minutos realizando a operação, podemos perceber pelo resultado abaixo que o espaço utilizado caiu para 2,76 MB.
SQL> SELECT SPACE_ALLOCATED_MB,
2 SPACE_USED_MB,
3 SPACE_ALLOCATED_MB - SPACE_USED_MB SPACE_RECLAIMABLE_MB
4 FROM
5 (
6 SELECT
7 (SELECT round(s.bytes/1024/1024,2) MB FROM dba_segments s JOIN dba_lobs l USING (owner, segment_name)
8 WHERE l.table_name = 'T1' AND OWNER='SCOTT') SPACE_ALLOCATED_MB,
9 (SELECT round(nvl((sum(dbms_lob.getlength(IMAGEM))),0)/1024/1024,2) MB FROM SCOTT.T1) SPACE_USED_MB
10 FROM DUAL);
SPACE_ALLOCATED_MB SPACE_USED_MB SPACE_RECLAIMABLE_MB
------------------ ------------- --------------------
3,81 2,76 1,05
1 linha selecionada.
Por fim, o espaço utilizado na tablespace foi reduzido de 372,06 MB para 6,93 MB.
SQL> SELECT a.tablespace_name,
2 trunc(b.used_percent,2) used_percent,
3 trunc(b.used_space * a.block_size/1024/1024,2) used_space_MB,
4 c.free_mb,trunc(b.tablespace_size * a.block_size/1024/1024,2) tablespace_max_size_MB
5 FROM dba_tablespaces a,
6 dba_tablespace_usage_metrics b,
7 ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_mb
8 FROM dba_free_space
9 GROUP BY tablespace_name) c
10 WHERE a.tablespace_name = b.tablespace_name
11 AND b.tablespace_name = c.tablespace_name
12 AND a.contents = 'PERMANENT'
13 AND a.tablespace_name = 'TBS_DATA';
TABLESPACE_NAME USED_PERCENT USED_SPACE_MB FREE_MB TABLESPACE_MAX_SIZE_MB
------------------------- ------------ ------------- ---------- ----------------------
TBS_DATA 1,34 6,93 507,6875 514,62
1 linha selecionada.
