| Unable to resize the datafile [message #413984] |
Mon, 20 July 2009 01:02  |
jesuisantony
Messages: 166
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  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
jesuisantony
Messages: 166
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 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
