Oracle Database Discussions

Hi all,

I use Oracle 10g and I have a little problem with releasing the free space on my disk. I shrinked all the tables in the tablespace and used the following script to determine the minimal size of the datafile:


select
file_name,
(hwm)block_size HWM_B,
(hwm)block_size/1024 HWM_KB,
blocksblock_size total_size,
(blocks-hwm+1)block_size shrinkage_possible_B,
(blocks-hwm+1)block_size/1024 shrinkage_possible_KB,
'alter database datafile '||a.file_id||' resize '||(hwm)block_size/1024||'K;' resize_script
from dba_data_files a,
( select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id ) b,
dba_tablespaces c
where a.file_id = b.file_id
and a.tablespace_name=c.tablespace_name
order by shrinkage_possible_B desc;


So, I use the generated script of the proper row (even add about 100MB to the minimal size).
It works for some datafile, but not for all of them. I have a few, where I get the this error: +'ORA-03297: file contains used data beyond requested RESIZE value'+

I guess my script isn't correct. Can you help me, what is wrong with it?

Thank you in advance.

Cheers,
Peter