Dynamically resize datafiles in Oracle

Ever wanted to resize to the smallest all of your datafiles at once?

[sourcecode language=’sql’]
set serveroutput on;

declare
cursor c2 is select file_name,
CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SMALLEST
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS
GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+);
my_df varchar2(200);
tmp_cmd varchar2(200);
smallest_size varchar2(8);

begin
open c2;

loop

fetch c2 into my_df, smallest_size;
exit when c2%notfound;
dbms_output.put_line(‘Resizing datafile ‘ || my_df || ‘ with ‘ || smallest_size || ‘M’);
tmp_cmd := ‘alter database datafile ”’ || my_df || ”’ resize ‘ || smallest_size || ‘M’;
execute immediate (tmp_cmd);

end loop;

close c2;
end;
/
[/sourcecode]

Replace the 8192 with whatever your db_block_size is.

Thanks to Diane Martin for her extremely useful article at http://www.articles.freemegazone.com/shrinking-oracle-datafiles.php.