In developpement, it can happen that you may want to shrink oracle datafiles after a huge delete (ie. drop user, drop big table, ...).
Here is a simple way to shrink datafiles using
RESIZE option, but it may not be the most efficient.
RESIZE datafiles to the minimal
The following script will generate a sql file to resize every datafiles to the minimal size possible with
Go further with RESIZE
RESIZE option can only shrink the datafile over the lastest block in use.
So it may happen that you won't be able to shrink a datafile even if less than 1% is used.
We can try to move objects at the beginning of the datafile in order to get a more efficient
Or even better is to move all object to a new tablespace then skrink all datafiles (move objects back if necessary)
Here is some SQL which can hel you to archieve that:
You may want to rebuild all unusable indexes after that:
More efficient way for temporary datafile
RESIZE option can be used for temporary datafile but it may not be very efficient with this kind of file.
- Check how to Shrink temporary datafile
Enjoyed this article? Please like it or share it.