Originally posted by niranjan
Hai all,

I tried to resize a datafile which was in autoextend mode.The file size is 2.5gb where as the contents of the file is only up to 1gb.So I tried to resize it to 1.5gb but I'm getting the error,
ORA-03297: file contains used data beyond requested RESIZE value

I tried to alter tablespace coalesce and then checked outwith dba_free_space.To my surprise there is no much change in the bytes and blocks of dba_free_space or dba_free_space_coalesced after coalescing.

Kindly let me know the possibilities of resizing my datafile.

regards,
Niranjan.
You cannot resize below the HWM of the file. This query will tell you what the HWM is for each file:

set lines 256
col "File Name" for A47
column file_name format a40;
column highwater format 9999999999;
SELECT Substr(df.file_name,1,47) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)"
FROM dba_data_files df,
(SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e,
(SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f,
(SELECT file_id, max(block_id) maximum from dba_extents group by file_id) b,
dba_extents c,
(SELECT value db_block_size from v$parameter where name='db_block_size') d
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
AND df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
ORDER BY
df.tablespace_name, df.file_name
/