Resizing datafile!

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.

As Mentioned in Oracle Documentation

ORA-03297 file contains used data beyond requested RESIZE value

Cause: Some portion of the file in the region to be trimmed is currently in use by a database object.

Action: Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed.

Hai Vishal,

I had a user before with default tablespace which is related to this datafile.now I changed that user to another tablespace and moved all its contents.So obviously there shouldn't be any problem now.After coalescing the tablespace surely the free space should be merged up and be summed up to fewer blocks,right?But why is that not happenning.
why the coalesce is not showing any effect on my tablespace?
Since the datafile size is 2.5gb,I tried to resize it very near ti its size like 2.2gb also but invain.help me.

Regards,
Niranjan.

Script to find database object locations for a given datafile.

http://metalink.oracle.com/metalink/...atabase_id=NOT

Quote:

---

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
/

Thanx for your support Julian.So can we use deallocate unused clause to free up the space?but still I had already moved that whole user schema in to another tablespace.So what can be done to resize it?
any other option?