Home » RDBMS Server » Server Administration » Resize Datafile
Resize Datafile [message #264217] Sun, 02 September 2007 01:50 Go to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
I have tablespace test1#data with two datafiles.
e.g.
e:\oradata\test11#data.ora having space 6000M and
g:\oradata\test12#data.ora having space 5000M

From enterprise manage i see that e:\oradata\test11#data.ora occupy space 5000M and g:\oradata\test12#data.ora occupy 4000M.
Now i want to resize datafile e:\oradata\test11#data.ora to 4500M and try to execute the following command
alter database datafile 'g:\oradata\test12#data.ora' resize 4500M


But i get the follwing error.
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


How i can resize the datafile?

I am using the following oracle version
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Re: Resize Datafile [message #264220 is a reply to message #264217] Sun, 02 September 2007 02:18 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
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.
Re: Resize Datafile [message #264222 is a reply to message #264217] Sun, 02 September 2007 02:50 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
i want to resize datafile e:\oradata\test11#data.ora to 4500M


You wanted to shrink/extend datafile located on e drive and you perform operation on datafile located on g drive.

You are in conflict with your own one.
Re: Resize Datafile [message #264243 is a reply to message #264217] Sun, 02 September 2007 05:57 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

alter database datafile 'g:\oradata\test12#data.ora' resize 4500M


it will be
alter database datafile 'e:\oradata\test11#data.ora' resize 4500M


There are some other point i forget to mention i had a schema and i drop all the object from that schema and import another database schema from separate database. But i were not drop tablespaces i just drop all the tables, view, indexes
Re: Resize Datafile [message #264245 is a reply to message #264217] Sun, 02 September 2007 06:10 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
But i were not drop tablespaces i just drop all the tables, view, indexes


What does it mean? By the way, to resize a data file " choose a resize value such that only free space is in the trimmed."
Re: Resize Datafile [message #264247 is a reply to message #264217] Sun, 02 September 2007 06:20 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

What does it mean? By the way, to resize a data file " choose a resize value such that only free space is in the trimmed."



I had a schama with tablespace test#data. Then i want to import another schema from other database to that schema. So i drop all the objects means table,view, index but i didnot drop that tablespace. Then i import that dump to the schema. But total data file were used less than before schema. Now i want to resize the data file but the error happen
Re: Resize Datafile [message #264250 is a reply to message #264217] Sun, 02 September 2007 06:29 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Data file is physical storage. Tablespace is logical storage.

Why don't you try

Either
1) change tablespace autoextend on and import then later autoextend off.
or,

2)Add another datafile in the tablespace.

[Updated on: Sun, 02 September 2007 06:41]

Report message to a moderator

Re: Resize Datafile [message #264504 is a reply to message #264217] Mon, 03 September 2007 10:10 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

ORA-03297: file contains used data beyond requested RESIZE value

http://dbataj.blogspot.com/2007/01/ora-03297.html

Previous Topic: auxillary
Next Topic: pwd file
Goto Forum:
  


Current Time: Fri Dec 09 17:27:11 CST 2016

Total time taken to generate the page: 0.12563 seconds