| resize datafile [message #325023] |
Wed, 04 June 2008 16:35  |
simoh
Messages: 6
Registered: May 2008
|
Junior Member |
|
|
Hi,
There is an application that runs on oracle 10.2.0.1.0. 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';
SUM(BYTES)/1024/1024
--------------------
7804.875
Also, I purged the recyclebin but still no luck.
Can anyone help me please.
Thank you in advance
|
|
|
|
|
|
|
|
| Re: resize datafile [message #327658 is a reply to message #325025] |
Tue, 17 June 2008 02:50  |
ora110
Messages: 42
Registered: September 2007
Location: China
|
Member |
|
|
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..
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
