| Resize Datafile [message #264217] |
Sun, 02 September 2007 01:50  |
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 #264243 is a reply to message #264217] |
Sun, 02 September 2007 05:57  |
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 #264247 is a reply to message #264217] |
Sun, 02 September 2007 06:20 |
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 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
