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
…
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
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;
<>
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.
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.
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
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!
