Home » RDBMS Server » Server Administration » Unable to resize the datafile (Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit)
Unable to resize the datafile [message #413984] Mon, 20 July 2009 01:02 Go to next message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Hi All,

Output from dba_data_files:
===========================
USERS 5 (GB)

Output from dba_segments:
==========================
USERS 0.496337890625 (GB)

Output from dba_free_space:
===========================
USERS 4.50360107421875 (GB)

There is only one datafile in USERS tablespace. So as per my understanding I should be able to reduce the datafile size to 1GB. But When I issue "alter database datafile 'users01.dbf' resize 1g" the following error message is displayed.

Error report:

SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 - "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.


Not sure why do I get this message. Please suggest me on this.

Regards,
Antony
Re: Unable to resize the datafile [message #413988 is a reply to message #413984] Mon, 20 July 2009 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please search BEFORE posting, this is a FAQ.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You get this message because "Some portion of the file in the region to be trimmed is currently in use by a database object".

Regards
Michel

[Updated on: Mon, 20 July 2009 01:07]

Report message to a moderator

Re: Unable to resize the datafile [message #414062 is a reply to message #413984] Mon, 20 July 2009 04:18 Go to previous messageGo to next message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
The Oracle Version is 10.2.0.2 (Enterprise Edition)

I have enabled row movement and executed the shrink compact commands for the tables in this tablespace. Still I am unable to resize the datafile to 1g.

Please help.

Regards,
Antony
Re: Unable to resize the datafile [message #414065 is a reply to message #414062] Mon, 20 July 2009 04:28 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is still a FAQ and all the answers than can be done have already been made, the details depend on information you are the only one to (be able to) know.

Regards
Michel
Previous Topic: transactional Lock type TX TM, UL (merged 2)
Next Topic: When to go for oracle 10g standard edition,,,!!!
Goto Forum:
  


Current Time: Sun Dec 04 13:02:06 CST 2016

Total time taken to generate the page: 0.11011 seconds