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 óbvia? 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:
1 2 3 4 5 6 7 8 9 10 11 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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. |
1 2 3 4 5 6 7 8 9 |
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.