Tablespace Used MB Free MB Total MB
------------------------------ ---------- ---------- ----------
DATA_S1 686 295 981
We have a table space named DATA_S1 which contains one data file. As we can see that around 300M space of the data file is unused. If I try to shrink the datafile by 200M by:
alter tablespace TABLESPACE_NAME datafile 'DATAFILE_NAME' resize 800M;
it gives:
ORA-03297: file contains used data beyond requested RESIZE value.
Is there anyway I can reclaim the space from datafile
Best Answer
Prior to resizing, you have to remove fragmentation in the datafile. That means every segment (sets of blocks assigned to each object in that tablespace) needs to bring together all rows inside their db blocks.
For this you have several options:
If you're on 11gR2 you can use
That will remove the fragmentation and shrink the associated datafiles plus it will set the HWM in the "lowest" possible position.
CAUTION: This is a n I/O intensive operation, never do it during business hours or outside a maintenance window for production environments.
Another option that can be used is the creation of a "backup as copy" of the fragmented datafiles and then switch the database to point those "image copies". Then drop the original and redo using the image copy as the original and backing them up "as copy" to the original location. It's a little longer and some more complex, but the "downtime" is just a few seconds while the switch is performed.
Refer to the Oracle Database 11.2 RMAN Reference Manual for more details on this option.
Hope this helps you.
If not, please add more detail to the question. Like version of the software, maintenance window time, accepted downtime, etc.