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
complete this part
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.
If so, read this article: shrink-temporary-datafile.html
Enjoyed this article? Please like it or share it.