Espaço em disco. Algo que pode te dar uma boa dor-de-cabeça.

Muitos já se viram frente à necessidade de mais espaço em uma ou outra tablespace.

Algo natural no dia-a-dia conforme o crescimento natural do banco de dados.

Nem sempre isso é um problema caso seu espaço disponível em disco possa suprir sua necessidade de aumento de uma tablespace em particular.

Este cenário torna-se um problema; Um problemão na verdade; quando o espaço em disco disponível para o crescimento de suas tablespaces é próximo de Zero.

De onde tirar espaço? Se sua tablespace atingir 100% de espaço em uso, muito provavelmente sua aplicação parará de funcionar devidamente.

Bem, raramente todas suas tablespaces atingirão 100% de uso ao mesmo tempo, então, por que não liberar algum espaço dessas tablespaces que contém algum espaço livre e incluir este espaço em sua tablespace problemática? Fácil, não? A solução mais óbiva? Bom, nem sempre.



O problema dessa manobra está justamente no espaço não utilizado pelos datafiles daquela tablespace com espaço livre.

Todo datafile possui o que chamamos de "High Water Mark" (Ou Marca D'água do nível Máximo).

Isso é bem fácil de ilustrar. Imagine um copo com água até um determinado ponto. Então você coloca um canudo neste copo e bebe um pouco dessa água. Naturalmente, no ponto onde havia água antes de ser bebida ficará uma "Marca d´água" onde antes foi o nível máximo de água.

Transferindo este exemplo aos datafiles de sua tablespace, essa "Marca D'água" será o ponto máximo onde uma vez houveram dados neste datafile.

Os blocos necessários para gravação em disco neste datafile já foram utilizados e, embora os dados tenham sido apagados, seu espaço já foi requisitado no disco e não pode ser reclamado novamente pelo banco.

Portanto, apenas por verificar o espaço disponível nos datafiles não quer dizer que será possível reclama-lo por completo ao SO.

Abaixo explico uma técnica para identificar o espaço livre em suas tablespaces e o espaço livre em seus datafiles, bem como suas respectivas Hight Water Mark.


Primeiro  identificamos o espaço disponível em banco.

Este script pode ser utilizado para isso:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
col "Tablespace" for a13
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select fs.tablespace_name "Tablespace",
    (df.totalspace - fs.freespace) "Used MB",
    fs.freespace "Free MB",
    df.totalspace "Total MB",
    round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
    (select tablespace_name,
        round(sum(bytes) / 1048576) TotalSpace
      from dba_data_files
      group by tablespace_name) df,
    (select tablespace_name,
        round(sum(bytes) / 1048576) FreeSpace
      from dba_free_space
      group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name
;


O resultado é algo parecido com isso:

Tablespace        Used MB     Free MB    Total MB  Pct. Free
------------- ----------- ----------- ----------- ----------
SYSAUX              1,379         161       1,540         10
UNDOTBS1               37       1,093       1,130         97
TS1                    31      29,969      30,000        100
USERS                   1         119         120         99
SYSTEM                717         283       1,000         28
UNDOTBS3               27       3,663       3,690         99
UNDOTBS2              114         816         930         88

7 rows selected.

Notem que o que está na coluna Free MB trata-se do espaço disponível para a tablespace mas não reflete o que já foi escrito e gravado na Hight Water Mark.


O procedimento abaixo pose ser utilizado para identificar o tamanho utilizado e disponível de todos os datafiles presentes no banco, informando inclusive até onde podem ser redimensionados.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

-- Primeiro Bloco: Identifica o tamanho de blksize e armazena em uma variável para uso posterior.
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

-- Segundo bloco. Mostra todos os datafiles do banco e mostra o tamanho utilizado, o tamanho total atual, e até quanto voce pode economizar com ele.
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/


Tenham Paciência, o resultado pode demorar um pouco dependendo do tamanho de seus datafiles.

O resultado será algo parecido com isso:

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
+DG_DATA_BPP/bpp/datafile/system.259.765636177          718    1,000      282
+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181        1,450    1,540       90
+DG_DATA_BPP/bpp/datafile/undotbs2.263.765636193        118      930      812
+DG_DATA_BPP/bpp/datafile/undotbs3.264.765636195         31    3,690    3,659
+DG_DATA_BPP/bpp/datafile/ts1.271.765711963              32   30,000   29,968
+DG_DATA_BPP/bpp/datafile/undotbs1.261.765636187         66    1,130    1,064
+DG_DATA_BPP/bpp/datafile/users.265.765636195             1      120      119
                                                                     --------
sum                                                                    35,994

7 rows selected.

A primeira coluna (FILE_NAME) exibe o Path completo do datafile.

A segunda coluna (Smallest Size Poss.) exibe o espaço já utilizado pelo datafile.

A terceira coluna (Current Size) exibe o tamanho atual do datafile.

E a terceira coluna (Poss. Savings) exibe o quanto pode ser devolvido ao disco e utilizado posteriormente em outro datafile.

Notem, por exemplo, o datafile sysaux.260.765636181. Este está indicando que está em uso 1450Mb pelo datafile.
No entanto, em nossa listagem anterior podemos notar que o espaço livre nessa tablespace é de 1379Mb, o que Ilustra a High Water Mark deste datafile. Portanto, nós conseguiremos reduzir este datafile até o tamanho de 1450Mb, recuperando 90Mb para o disco e, ainda assim, a listagem de tablespaces exibirá um espaço livre de 71Mb para a tablespace Sysaux, conforme ilustrado abaixo:

Primeiro a ilustração de não ser possível redimensionar a tablespace abaixo da high water mark:

1
2
3
4
5
SQL> alter database datafile '+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181' resize 1379m;
alter database datafile '+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181' resize 1379m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


Em seguida, quando utilizamos como novo tamanho a posição atual da high water mark, podemos reduzir ao máximo permitido este datafile, recuperando um bom espaço para o disco:

1
2
3
SQL> alter database datafile '+DG_DATA_BPP/bpp/datafile/sysaux.260.765636181' resize 1450m;

Database altered.


Tablespace        Used MB     Free MB    Total MB  Pct. Free
------------- ----------- ----------- ----------- ----------
SYSAUX              1,379          71       1,450          5
UNDOTBS1               37       1,093       1,130         97
TS1                    31      29,969      30,000        100
USERS                   1         119         120         99
SYSTEM                717         283       1,000         28
UNDOTBS3               28       3,662       3,690         99
UNDOTBS2              114         816         930         88


Espero que tenham gostado e que essa informação seja útil.

Forte abraço a todos.

Comentem a vontade.

Share this:

SOBRE O AUTOR

Com uma década de experiẽncia em produtos Oracle como DBA e também Developer, pretendo, com esse blog, trocar experiências com você, leitor.

3 comentários:

  1. Curtiu né?
    Esse script eu adaptei de um que peguei no site asktom.oracle.com.
    Passei para o pessoal do time ano passado... fez sucesso com a galera :D

    ResponderExcluir
  2. opa... parece que eu vi nome no aqui :-)

    Você gostou hein José da adaptação?!?!?!

    Parabéns pelo Evandro.

    ResponderExcluir