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
;

 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(+)
/

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

1
2
3

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

Database altered.