Realizando SHRINK em segmentos LOB para liberar espaço na tablespace (SHRINK LOB):Database Blog

quarta-feira, 19 de setembro de 2018

Realizando SHRINK em segmentos LOB para liberar espaço na tablespace (SHRINK LOB)

Por Eduardo Legatti

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.