Printable View
Is it possible to defragment a datafile ??
I've a datafile of 5 GB.. I checked dba_segments and found that it has only 880 MB occupied in that datafile .
When i tried to resize the datafile to 1 GB , it throws an error "ORA-03297: file contains used data beyond requested RESIZE value"
Can anybody tell me how can i reduce this datafile size.
Regards
Saravana Kumar
All Oracle is telling you is that the high water mark is greater than 1gb. It does not mean that the datafile is fragmented. In fact if you are using Locally Managed Tablespaces, I would bet that you do not have any issue with fragmentation. Try resizing it to 2gb instead.Quote:
Originally Posted by ksarav
Thats true. High water mark is more that 4 gb . I cant even reduce the file from 5 gb to 4 gb .
But in dba_segments i can see that the file has ony 880 mb occupied space.
Any idea how can i reduce the space to 1 GB .
What objects are in the tablespace? Can you move them to a different tablespace? Once you do that you will not have any problems shrinking the datafile.Quote:
Originally Posted by ksarav
Why is it so important that you shrink this datafile?
The tablespace has 6 files like this where i cant reduce the resize. I've more than 20 GB free space left in that tablespace. If i can know how to resize one datafile..i can resize the other files also ...
you have objects at the end of the tablespace - you will need to move them if you want to resize any datafiles.
Wont gain anything though
Well, hard to say. It depends on what you mean by "gain". If you only have 1GB of data in a tablespace that once occupied 6gb of space, you are effectively backing up 5gb of empty space (using rman).Quote:
Originally Posted by davey23uk