Script para execução de resize em datafiles do banco de dados Oracle
Olá,
Segue o script utilizado no post “Diminuindo físicamente um banco de dados Oracle” para consulta.
Resize_seguro
SQL> get c:\DBA\scripts\resize_seguro.sql
1 select file_name, 2 ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest, 3 ceil( blocks*8192/1024/1024) currsize, 4 ceil( blocks*8192/1024/1024) - 5 ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings, 6 ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) smallest_safe, 7 ceil( blocks*8192/1024/1024) - 8 ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) savings_safe 9 from dba_data_files a, 10 ( select file_id, max(block_id+blocks-1) hwm 11 from dba_extents 12 group by file_id ) b 13 where a.file_id = b.file_id(+) 14 and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) < ceil( blocks*8192/1024/1024) 15 and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) > 100 16 order by 4 desc; 17 select 'alter database datafile ''' || file_name || ''' resize ' || 18 ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) || 'm;' cmd 19 from dba_data_files a, 20 ( select file_id, max(block_id+blocks-1) hwm 21 from dba_extents 22 group by file_id ) b 23 where a.file_id = b.file_id(+) 24 and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) < ceil( blocks*8192/1024/1024) 25 and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) > 100
O resultado será igual ao abaixo:
SQL> @resize_seguro
FILE_NAME SMALLEST CURRSIZE SAVINGS SMALLEST_SAFE SAVINGS_SAFE ------------------------------------------------------------ ---------- ---------- ---------- ------------- ------------ /u02/app/oracle/oradata/finp/sysaux01.dbf 191 2000 1809 229 1771 /u02/app/oracle/oradata/finp/system01.dbf 264 2000 1736 316 1684
CMD ------------------------------------------------------------------------------------------------------------------------ alter database datafile '/u02/app/oracle/oradata/finp/system01.dbf' resize 316m; alter database datafile '/u02/app/oracle/oradata/finp/sysaux01.dbf' resize 229m;
Todos as colunas foram comentadas no POST que foi utilizado o script, para melhor o entendimento do resultado gerado.
Abraços