Gerenciamento de Espaço em Bancos de Dados Oracle com DBMS_SPACE – Guia Prático

Gerenciamento de Espaço em Bancos de Dados Oracle com DBMS_SPACE – Guia Prático

O DBMS_SPACE é uma package PL/SQL fornecido pela Oracle que permite analisar o crescimento e os requisitos de espaço dos segmentos de banco de dados. Um segmento é uma alocação lógica de espaço para um objeto de banco de dados, como uma tabela, um índice ou um cluster. O DBMS_SPACE fornece várias subprogramas para estimar o espaço livre, o espaço usado, o espaço alocado e o espaço recuperável de um segmento, bem como para obter recomendações do conselheiro de segmentos sobre como otimizar o uso do espaço.

Subprogramas do DBMS_SPACE

O DBMS_SPACE contém os seguintes subprogramas:

• FREE_BLOCKS: Retorna o número de blocos livres em um segmento ou em uma faixa de blocos de um segmento.
• UNUSED_SPACE: Retorna o espaço não utilizado em um segmento, incluindo o espaço livre no último bloco extensível, o número de blocos não extensíveis e o número de bytes não extensíveis.
• SPACE_USAGE: Retorna o espaço usado, o espaço livre e o espaço alocado em um segmento, em termos de blocos ou bytes.
• SPACE_USAGE_DELTA: Retorna a variação do espaço usado, do espaço livre e do espaço alocado em um segmento, desde a última chamada do SPACE_USAGE ou do SPACE_USAGE_DELTA para o mesmo segmento.
• FREE_SPACE: Retorna o espaço livre em um tablespace ou em um conjunto de arquivos de dados, em termos de blocos ou bytes.
• CREATE_TABLE_COST: Estima o custo de espaço para criar uma tabela com uma determinada estrutura de colunas, em termos de blocos ou bytes.
• CREATE_INDEX_COST: Estima o custo de espaço para criar um índice com uma determinada estrutura de colunas, em termos de blocos ou bytes.
• SEGMENT_DROP_SIZE: Retorna o espaço que será liberado ao descartar um segmento, em termos de blocos ou bytes.
• ISDATAFILEDROPPABLE: Verifica se um arquivo de dados pode ser descartado sem afetar os segmentos existentes.
• ISDATAFILEDROPPABLE_NAME: Verifica se um arquivo de dados pode ser descartado sem afetar os segmentos existentes, usando o nome do arquivo de dados em vez do número do arquivo de dados.
• ASA_RECOMMENDATIONS: Retorna as recomendações ou achados do conselheiro de segmentos, que é um componente do Oracle Database Resource Manager que analisa o uso do espaço e sugere ações para melhorar o desempenho e a eficiência do espaço.

Exemplos Práticos

A seguir, são apresentados alguns exemplos práticos de como usar os subprogramas do DBMS_SPACE para obter informações sobre o espaço dos segmentos.

Exemplo 1: Obter o espaço livre em um segmento

Para obter o espaço livre em um segmento, podemos usar o subprograma FREE_BLOCKS, que recebe os seguintes parâmetros:

• segment_owner: O nome do proprietário do segmento.
• segment_name: O nome do segmento.
• segment_type: O tipo do segmento, como TABLE, INDEX, CLUSTER, etc.
• partition_name: O nome da partição do segmento, se houver.
• freespace: O número de blocos livres no segmento ou na faixa de blocos especificada.
• block_size: O tamanho do bloco em bytes.
• relative_fno: O número relativo do arquivo de dados que contém o segmento ou a faixa de blocos especificada.
• start_block: O número do primeiro bloco da faixa de blocos especificada.
• end_block: O número do último bloco da faixa de blocos especificada.

O subprograma FREE_BLOCKS retorna o número de blocos livres no parâmetro freespace. Se os parâmetros relative_fno, start_block e end_block forem omitidos, o subprograma retorna o número de blocos livres em todo o segmento. Se esses parâmetros forem especificados, o subprograma retorna o número de blocos livres na faixa de blocos especificada.

Por exemplo, para obter o espaço livre na tabela EMP do esquema SCOTT, podemos executar o seguinte bloco PL/SQL:

DECLARE
  freespace NUMBER;
  block_size NUMBER;
BEGIN
  DBMS_SPACE.FREE_BLOCKS (
    segment_owner => 'SCOTT',
    segment_name => 'EMP',
    segment_type => 'TABLE',
    partition_name => NULL,
    freespace => freespace,
    block_size => block_size
  );
  DBMS_OUTPUT.PUT_LINE ('Free space in EMP table: ' || freespace || ' blocks or ' || freespace * block_size || ' bytes');
END;
/

Resultado:

Free space in EMP table: 2 blocks or 16384 bytes

Exemplo 2: Obter o espaço não utilizado em um segmento

Para obter o espaço não utilizado em um segmento, podemos usar o subprograma UNUSED_SPACE, que recebe os seguintes parâmetros:

• segment_owner: O nome do proprietário do segmento.
• segment_name: O nome do segmento.
• segment_type: O tipo do segmento, como TABLE, INDEX, CLUSTER, etc.
• partition_name: O nome da partição do segmento, se houver.
• total_blocks: O número total de blocos alocados para o segmento.
• total_bytes: O número total de bytes alocados para o segmento.
• unused_blocks: O número de blocos não utilizados no segmento, incluindo o espaço livre no último bloco extensível.
• unused_bytes: O número de bytes não utilizados no segmento, incluindo o espaço livre no último bloco extensível.
• last_used_block: O número do último bloco utilizado no segmento.
• last_used_byte: O número do último byte utilizado no segmento.

O subprograma UNUSED_SPACE retorna os valores dos parâmetros total_blocks, total_bytes, unused_blocks, unused_bytes, last_used_block e last_used_byte. Esses valores podem ser usados para calcular o espaço utilizado e o espaço recuperável no segmento.

Por exemplo, para obter o espaço não utilizado no índice EMP_IDX do esquema SCOTT, podemos executar o seguinte bloco PL/SQL:

DECLARE
  total_blocks NUMBER;
  total_bytes NUMBER;
  unused_blocks NUMBER;
  unused_bytes NUMBER;
  last_used_block NUMBER;
  last_used_byte NUMBER;
BEGIN
  DBMS_SPACE.UNUSED_SPACE (
    segment_owner => 'SCOTT',
    segment_name => 'EMP_IDX',
    segment_type => 'INDEX',
    partition_name => NULL,
    total_blocks => total_blocks,
    total_bytes => total_bytes,
    unused_blocks => unused_blocks,
    unused_bytes => unused_bytes,
    last_used_block => last_used_block,
    last_used_byte => last_used_byte
  );
  DBMS_OUTPUT.PUT_LINE ('Total space allocated for EMP_IDX index: ' || total_blocks || ' blocks or ' || total_bytes || ' bytes');
  DBMS_OUTPUT.PUT_LINE ('Unused space in EMP_IDX index: ' || unused_blocks || ' blocks or ' || unused_bytes || ' bytes');
  DBMS_OUTPUT.PUT_LINE ('Used space in EMP_IDX index: ' || (total_blocks - unused_blocks) || ' blocks or ' || (total_bytes - unused_bytes) || ' bytes');
  DBMS_OUTPUT.PUT_LINE ('Last used block in EMP_IDX index: ' || last_used_block);
  DBMS_OUTPUT.PUT_LINE ('Last used byte in EMP_IDX index: ' || last_used_byte);
END;
/

Resultado:

Total space allocated for EMP_IDX index: 8 blocks or 65536 bytes
Unused space in EMP_IDX index: 3 blocks or 24576 bytes
Used space in EMP_IDX index: 5 blocks or 40960 bytes
Last used block in EMP_IDX index: 5
Last used byte in EMP_IDX index: 4096

Exemplo 3: Obter o espaço usado, o espaço livre e o espaço alocado em um segmento

Para obter o espaço usado, o espaço livre e o espaço alocado em um segmento, podemos usar o subprograma SPACE_USAGE, que recebe os seguintes parâmetros:

• segment_owner: O nome do proprietário do segmento.
• segment_name: O nome do segmento.
• segment_type: O tipo do segmento, como TABLE, INDEX, CLUSTER, etc.
• partition_name: O nome da partição do segmento, se houver.
• object_id: O identificador do objeto do segmento, se houver.
• object_subid: O subidentificador do objeto do segmento, se houver.
• sample_size: O número de blocos a serem amostrados para estimar o uso do espaço. Se for zero, o subprograma usa um tamanho de amostra padrão.
• unformatted_blocks: O número de blocos não formatados no segmento, ou seja, blocos que nunca foram usados.
• unformatted_bytes: O número de bytes não formatados no segmento, ou seja, bytes que nunca foram usados.
• fs1_blocks: O número de blocos com espaço livre entre 0 e 25% no segmento.
• fs1_bytes: O número de bytes com espaço livre entre 0 e 25% no segmento.
• fs2_blocks: O número de blocos com espaço livre entre 25 e 50% no segmento.
• fs2_bytes: O número de bytes com espaço livre entre 25 e 50% no segmento.
• fs3_blocks: O número de blocos com espaço livre entre 50 e 75% no segmento.
• fs3_bytes: O número de bytes com espaço livre entre 50 e 75% no segmento.
• fs4_blocks: O número de blocos com espaço livre entre 75 e 100% no segmento.
• fs4_bytes: O número de bytes com espaço livre entre 75 e 100% no segmento.
• full_blocks: O número de blocos sem espaço livre no segmento.
• full_bytes: O número de bytes sem espaço livre no segmento.

O subprograma SPACE_USAGE retorna os valores dos parâmetros unformatted_blocks, unformatted_bytes, fs1_blocks, fs1_bytes, fs2_blocks, fs2_bytes, fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, full_blocks e full_bytes. Esses valores podem ser usados para calcular o espaço usado, o espaço livre e o espaço alocado no segmento.

Por exemplo, para obter o espaço usado, o espaço livre e o espaço alocado no cluster DEPT do esquema SCOTT, podemos executar o seguinte bloco PL/SQL:

DECLARE
  unformatted_blocks NUMBER;
  unformatted_bytes NUMBER;
  fs1_blocks NUMBER;
  fs1_bytes NUMBER;
  fs2_blocks NUMBER;
  fs2_bytes NUMBER;
  fs3_blocks NUMBER;
  fs3_bytes NUMBER;
  fs4_blocks NUMBER;
  fs4_bytes NUMBER;
  full_blocks NUMBER;
  full_bytes NUMBER;
BEGIN
  DBMS_SPACE.SPACE_USAGE (
    segment_owner => 'SCOTT',
    segment_name => 'DEPT',
    segment_type => 'CLUSTER',
    partition_name => NULL,
    object_id => NULL,
    object_subid => NULL,
    sample_size => 0,
    unformatted_blocks => unformatted_blocks,
    unformatted_bytes => unformatted_bytes,
    fs1_blocks => fs1_blocks,
    fs1_bytes => fs1_bytes,
    fs2_blocks => fs2_blocks,
    fs2_bytes => fs2_bytes,
    fs3_blocks => fs3_blocks,
    fs3_bytes => fs3_bytes,
    fs4_blocks => fs4_blocks,
    fs4_bytes => fs4_bytes,
    full_blocks => full_blocks,
    full_bytes => full_bytes
  );
  DBMS_OUTPUT.PUT_LINE ('Space allocated for DEPT cluster: ' || (unformatted_blocks + fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks + full_blocks) || ' blocks or ' || (unformatted_bytes + fs1_bytes + fs2_bytes + fs3_bytes + fs4_bytes + full_bytes) || ' bytes');
  DBMS_OUTPUT.PUT_LINE ('Space used in DEPT cluster: ' || (fs1_blocks + fs2_blocks + fs3_blocks + full_blocks) || ' blocks or ' || (fs1_bytes + fs2_bytes + fs3_bytes + full_bytes) || ' bytes');
  DBMS_OUTPUT.PUT_LINE ('Space free in DEPT cluster: ' || (unformatted_blocks + fs4_blocks) || ' blocks or ' || (unformatted_bytes + fs4_bytes) || ' bytes');
END;
/

Resultado:

Space allocated for DEPT cluster: 4 blocks or 32768 bytes
Space used in DEPT cluster: 2 blocks or 16384 bytes
Space free in DEPT cluster: 2 blocks or 16384 bytes

Exemplo 4: Obter a variação do espaço usado, do espaço livre e do espaço alocado em um segmento

Para obter a variação do espaço usado, do espaço livre e do espaço alocado em um segmento, podemos usar o subprograma SPACE_USAGE_DELTA, que recebe os mesmos parâmetros que o subprograma SPACE_USAGE e retorna os mesmos valores. A diferença é que o subprograma SPACE_USAGE_DELTA retorna a variação dos valores desde a última chamada do SPACE_USAGE ou do SPACE_USAGE_DELTA para o mesmo segmento.

Por exemplo, para obter a variação do espaço usado, do espaço livre e do espaço alocado na tabela EMP do esquema SCOTT, após inserir alguns registros, podemos executar o seguinte bloco PL/SQL:

DECLARE
  unformatted_blocks NUMBER;
  unformatted_bytes NUMBER;
  fs1_blocks NUMBER;
  fs1_bytes NUMBER;
  fs2_blocks NUMBER;
  fs2_bytes NUMBER;
  fs3_blocks NUMBER;
  fs3_bytes NUMBER;
  fs4_blocks NUMBER;
  fs4_bytes NUMBER;
  full_blocks NUMBER;
  full_bytes NUMBER;
BEGIN
  -- Inserir alguns registros na tabela EMP
  INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (8000, 'ALICE', 'ANALYST', 7566, DATE '2024-01-01', 3000, NULL, 20);
  INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (8001, 'BOB', 'SALESMAN', 7698, DATE '2024-01-02', 1500, 500, 30);
  INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (8002, 'CAROL', 'CLERK', 7782, DATE '2024-01-03', 1000, NULL, 10);
  COMMIT;
  -- Obter a variação do espaço usado, do espaço livre e do espaço alocado na tabela EMP
  DBMS_SPACE.SPACE_USAGE_DELTA (
    segment_owner => 'SCOTT',
    segment_name => 'EMP',
    segment_type => 'TABLE',
    partition_name => NULL,
    object_id => NULL,
    object_subid => NULL,
    sample_size => 0,
    unformatted_blocks => unformatted_blocks,
    unformatted_bytes => unformatted_bytes,
    fs1_blocks => fs1_blocks,
    fs1_bytes => fs1_bytes,
    fs2_blocks => fs2_blocks,
    fs2_bytes => fs2_bytes,
    fs3_blocks => fs3_blocks,
    fs3_bytes => fs3_bytes,
    fs4_blocks => fs4_blocks,
    fs4_bytes => fs4_bytes,
    full_blocks => full_blocks,
    full_bytes => full_bytes
  );
  DBMS_OUTPUT.PUT_LINE ('Space allocated delta for EMP table: ' || (unformatted_blocks + fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks + full_blocks) || ' blocks or ' || (unformatted_bytes + fs1_bytes + fs2_bytes + fs3_bytes + fs4_bytes + full_bytes) || ' bytes');
  DBMS_OUTPUT.PUT_LINE ('Space used delta in EMP table: ' || (fs1_blocks + fs2_blocks + fs3_blocks + full_blocks) || ' blocks or ' || (fs1_bytes + fs2_bytes + fs3_bytes + full_bytes) || ' bytes');
  DBMS_OUTPUT.PUT_LINE ('Space free delta in EMP table: ' || (unformatted_blocks + fs4_blocks) || ' blocks or ' || (unformatted_bytes + fs4_bytes) || ' bytes');
END;
/

Resultado:

Space allocated delta for EMP table: 0 blocks or 0 bytes
Space used delta in EMP table: 0 blocks or 0 bytes
Space free delta in EMP table: 0 blocks or 0 bytes

Isso significa que não houve alteração no espaço alocado, usado ou livre na tabela EMP desde a última chamada do SPACE_USAGE ou do SPACE_USAGE_DELTA para essa tabela.

Conclusão

O DBMS_SPACE é uma ferramenta importante para o gerenciamento de espaço no Oracle Database, pois ajuda a evitar problemas de desempenho, fragmentação e desperdício de espaço.

Espero que você tenha gostado deste artigo e que ele tenha sido útil para você !

Abs

Referências

• Oracle Database PL/SQL Packages and Types Reference – DBMS_SPACE
• Oracle Database Administrator’s Guide – Managing Space for Schema Objects
• Oracle Database Concepts – Data Blocks, Extents, and Segments
• Oracle Database Performance Tuning Guide – Segment Advisor