Systems Engineering and RDBMS

Script for calculation of HWM (High WaterMark) of Data Files

Posted by decipherinfosys on May 15, 2007

Oracle Datafiles cannot be re-sized below the HWM of segment. Here is a script which will give the SQLs for the datafiles that can be shrink.

rem     Purpose:        Calculation of HighwaterMark of datafiles
rem
rem     Comments:       minimal size of a datafile is 2 Oracle blocks
rem                     resizing should always be a multiple of Oracle blocks
rem
rem     Privileges:     select on sys.dba_data_files
rem                     select on sys.dba_free_space
rem                     select on sys.v_$parameter
rem
rem     ——————————————————————–

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

/* get the blocksize of the database, needed to calculate the startaddress */

select value
into   blocksize
from   v$parameter
where  name = ‘db_block_size’;

/* retrieve all datafiles */

for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
<<outer>>
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
/* in order to shrink the free space must be uptil end of file */
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(‘Can not be resized, no free space at end of file.’);
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(‘Can be resized uptil: ‘||2*blocksize||’ Bytes, Actual size: ‘||c_rec1.bytes||’ Bytes’);
–dbms_output.put_line(‘ALTER DATABASE DATAFILE ”’||c_rec1.file_name ||”’ RESIZE ‘||ceil(filesize/(1024*1024))||’M;’);
dbms_output.put_line(‘.’);
else
dbms_output.put_line(‘Tablespace: ‘||’ ‘||c_rec1.tablespace_name||’ Datafile: ‘||c_rec1.file_name);
dbms_output.put_line(‘Can be resized uptil: ‘||filesize||’ Bytes, Actual size: ‘||c_rec1.bytes);
if (filesize – c_rec1.bytes) > 52428800 — Resize more than 50 mb
then
dbms_output.put_line(‘ALTER DATABASE DATAFILE ”’||c_rec1.file_name ||”’ RESIZE ‘||ceil(filesize/(1024*1024))||’M;’);
end if;
dbms_output.put_line(‘.’);
end if;
end if;
end loop;
end;
/

Sorry, the comment form is closed at this time.

 
%d bloggers like this: