Reduzindo o tamanho de um datafile no Oracle

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (2)  (0)

Veja como reduzir o tamanho de um datafile no Oracle.

Introdução

por Rodrigo Salviatto

Você já precisou redimensionar os datafiles dos tablespaces existentes no banco de dados Oracle de um sistema em produção? Quando necessitamos expandir o tablespace, é sempre muito fácil, criamos novos datafiles no disco ou incrementamos os datafiles existentes, passando de 500MB para 1GB por exemplo. Mas e se você precisar reduzir o tamanho de um determinado datafile que está com espaço alocado desnecessário? Como devemos proceder? Veremos neste artigo alguns exemplos, de como efetuar esta tarefa de forma rápida e segura.

 

Identificando o tamanho do datafile

Vamos imaginar a seguinte situação: você possui um tablespace chamado USERS. Este, por sua vez, possui 100MB alocados porém apenas 20MB utilizados, conforme apresentado na listagem 1.

 

SQL>select substr(a.tablespace_name,1,20) "Tablespaces",

  2       (b.BYTES/1048576) as "TotalMB",

  3       (b.BYTES/1048576)-(c.BYTES/1048576) as "UsedMB",

  4       (c.BYTES/1048576) as "FreeMB"

  5  from dba_tablespaces a,

  6       (select tablespace_name,sum(bytes) as "BYTES"

  7                  from dba_data_files

  8                  group by tablespace_name ) b,

  9       (select tablespace_name,sum(bytes) as "BYTES"

 10                  from dba_free_space 

 11                  group by tablespace_name) c

 12  where

 13     a.tablespace_name = b.tablespace_name(+)

 14     and b.tablespace_name = c.tablespace_name(+)

 15     order by a.tablespace_name;

SQL>

Tablespaces            TotalMB      UsedMB     FreeMB

-------------------- ---------- ---------- ----------

USERS                       100     20.625     79.375

Listagem 1. Espaço utilizado no tablespace USERS.

Caso necessitarmos reduzir o tamanho do tablespace USERS de 100MB para 21MB, executamos o comando da listagem 2.

SQL> ALTER DATABASE DATAFILE ‘C:\ORACLE\ORADATA\USERS.DBF’ RESIZE 21M;

*

ERROR at line 1:

ORA-03214: File Size specified is smaller than minimum required

Listagem 2. Procedimento para reduzir o tamanho do datafile da tablespace USERS

Por que ocorreu o erro ORA-03214? Como solucionar este problema?

Este é exatamente o propósito deste artigo, informar a você como proceder para identificar qual o real tamanho alocado do tablespace. Veja o bloco de comando PL/SQL, apresentado na listagem 2, utilizado para identificar qual o tamanho mínimo requerido para reduzir o datafile do tablespace USERS.

set serveroutput on

execute dbms_output.enable(2000000);

 

declare

  cursor c_dbfile is

        select  tablespace_name

                ,file_name

                ,file_id

                ,bytes

        from    sys.dba_data_files

        where   status !='INVALID'

        order   by tablespace_name,file_id;

 

  cursor c_space(v_file_id in number) is

        select block_id,blocks

        from   sys.dba_free_space

        where  file_id=v_file_id

        order  by block_id desc;

 

blocksize       binary_integer;

filesize        binary_integer;

extsize         binary_integer;

 

begin

  select value

  into   blocksize

  from   v$parameter

  where  name = 'db_block_size';

  for c_rec1 in c_dbfile

  loop

    filesize := c_rec1.bytes;

    <<outer>>

    for c_rec2 in c_space(c_rec1.file_id)

    loop

      extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);

      if extsize = filesize

      then

        filesize := (c_rec2.block_id - 1)*blocksize;

      else

        exit outer;

      end if;

    end loop outer;

    if filesize = c_rec1.bytes

    then

      dbms_output.put_line('Tablespace: '

      ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);

      dbms_output.put_line('Nao pode ser redimensionado.')

;

      dbms_output.put_line('.');

    else

      if filesize < 2*blocksize

      then

        dbms_output.put_line('Tablespace: '

        ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);

        dbms_output.put_line('Pode ser redimensionado até: '||2*blocksize

        ||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');

        dbms_output.put_line('.');

      else

        dbms_output.put_line('Tablespace: '

        ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);

        dbms_output.put_line('Pode ser redimensionado até: '||filesize

        ||' Bytes, Actual size: '||c_rec1.bytes);

        dbms_output.put_line('.');

      end if;

    end if;

  end loop;

end;

/

 

Tablespace:  USERS Datafile: C:\ORACLE\ORADATA\USERS.DBF

Pode ser redimensionado até: 22085632 Bytes, Actual size: 104857600

 

PL/SQL procedure successfully completed.

Listagem 2. Procedimento para identificar qual o limite em BYTES para reduzir o tablespace USERS

Conforme apresentado na listagem 2, o datafile da tablespace USERS, pode ser redimenionado até 22MB, portanto, se executarmos novamente o comando da listagem 1, obteremos êxito na tarefa para redimensionar o tablespace USERS. Vejamos exemplo na listagem 3

SQL> ALTER DATABASE DATAFILE ‘C:\ORACLE\ORADATA\USERS.DBF’ RESIZE 22085632;

 

Database altered.

 

Listagem 3. Efetuando o resize no tablespace USERS com o tamanho mínimo utilizado pelo Oracle.

Vejamos na listagem 4, o resultado do tamanho da tablespace USERS, após efetuar o resize com o tamanho mínimo requerido.

SQL> select substr(a.tablespace_name,1,20) "Tablespaces",

  2         (b.BYTES/1048576) as "TotalMB",

  3         (b.BYTES/1048576)-(c.BYTES/1048576) as "UsedMB",

  4         (c.BYTES/1048576) as "FreeMB"

  5    from dba_tablespaces a,

  6         (select tablespace_name,sum(bytes) as "BYTES"

  7                from dba_data_files

  8                group by tablespace_name ) b,

  9         (select tablespace_name,sum(bytes) as "BYTES"

 10                from dba_free_space

 11                group by tablespace_name) c

 12    where

 13       a.tablespace_name = b.tablespace_name(+)

 14      and b.tablespace_name = c.tablespace_name(+)

 15*      order by a.tablespace_name

SQL> /

 

Tablespaces             TotalMB     UsedMB     FreeMB

-------------------- ---------- ---------- ----------

USERS                   21.0625     20.625      1.375

Listagem 4. Visualizando o resultado do redimensionamento do tablespaces USERS.

Conclusão

Verificar o tamanho do datafile, através do bloco PL/SQL da listagem 2, fará com que você execute a tarefa de redimensionamento de um determinado datafile ( reduzir o tamanho do arquivo por exemplo), de forma segura e simples. Boa sorte e sucesso!

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?