Home » RDBMS Server » Server Administration » resize datafile
resize datafile [message #325023] Wed, 04 June 2008 16:35 Go to next message
Messages: 6
Registered: May 2008
Junior Member

There is an application that runs on oracle We were facing a problem with 2 tablespaces. They have been filling up the data files so rapidly. I contacted the support of that application several times and they assured me that the only solution is to keep adding more data files.
After a lot of research and troubleshooting, we discovered that there was a bug on the application. After the work around, the tablespaces size went incredible down.
The problem is that each tablespaces uses 9 data files each one is 30G. Which means that oracle reserves around 250G for each tablespace. Actually, after the work around, it is only uses 25% of that 250G.
The data is distributed among all those 9 data files-in small chunks. I need help to reduce the datafile size. Please note that this is a production server-it can’t be down at all.
I used the command
alter database datafile 'DATAFILE' resize 15G;
However, I ended up with errors:
ORA-03297: file contains used data beyond requested RESIZE value
I ran the following command to check the size on one of the datafile, that I tried to shrink from 30G to 15G, and it turned out to be only 7G
SQL> select sum(bytes)/1024/1024 from dba_extents where FILE_ID = '18';
Also, I purged the recyclebin but still no luck.
Can anyone help me please.

Thank you in advance
Re: resize datafile [message #325024 is a reply to message #325023] Wed, 04 June 2008 16:47 Go to previous messageGo to next message
Messages: 25581
Registered: January 2009
Location: SoCal
Senior Member
Hard Spot<=>simoh<=>Rock
yes, you are stuck between a rock & a hard spot. Poor you.

The sad reality is your only choice is to move all the objects out of that tablespace (TS) & into a new TS & then drop the old TS.
Then if you are so inclined, you can (re)create the TS & move the objects back into it.

All of this can be done using DBMS_REDEFINITION online.

Hope This Helps.
Have A Nice Day!
Re: resize datafile [message #325025 is a reply to message #325023] Wed, 04 June 2008 16:55 Go to previous messageGo to next message
Messages: 6
Registered: May 2008
Junior Member
i guess i am going to leave it as it is. and just find more disk space for the backup...
thanks for the help though. Very Happy
Re: resize datafile [message #327658 is a reply to message #325025] Tue, 17 June 2008 02:50 Go to previous message
Messages: 42
Registered: September 2007
Location: China

you are really a poor man .
in fact ,our factory faced this issue before .
so ,we moved all objects into another tablespace
it is a very big decision for our factory ....the IO system can't work for many hours..
Previous Topic: Upgrade from 10g to 11g
Next Topic: How to query for the executed statement?
Goto Forum:

Current Time: Thu Aug 24 05:43:45 CDT 2017

Total time taken to generate the page: 0.12431 seconds