Mistakes Happen – How To Cancel Alter Database Datafile Resize Command
You are running a datapump import into a database, and the UNDO tablespace running out of free space (more on that, in another post).
As you notice the space being chewed up by the import process, you open up another putty session, connect to the database,
and issue the alter command to resize the datafile to an adequate size.
ALTER DATABASE DATAFILE '/oradata/hrtst/dbf/undotbs01.dbf' RESIZE 10215100M
You wipe the sweat of your forehead, let out a big sigh, with relief. It was close, but you made it, you just extended the UNDO datafile in time, prior to getting full, and prior to the import throwing errors.
And then…You take another look, something doesn’t seem right.
The prompt of your last resize command didn’t come back.
You start counting the digits in the number of MB you typed in for the resize statements. 1,2,3,4,5,6…wait a minute there are too many digits.
You start counting again, but this time you count the digits in groups of 3 starting from the right to the left. And then it dawns on you, instead of resizing the datafile to 1TB, you added an extra 0 at the end, and Oracle is trying to resize the datafile to 10TB.
The problem now is, you do not have 10TB of free disk space available.
Did this ever happened to you? An extra 0 at the end of a number makes a huge difference: 1TB or 10TB.
What do you do now?
First of all try not to panic. What’s the worst that can happen?
Take a deep breath, your brain needs the oxygen.
Most likely you will try to identify the session that is running the resize command and kill it.
Rest assured that the session will go in a state of killed, and it will not be terminated very quickly. Now you are even more worried because you don’t know if you’re going to run out of space or not.
Here’s what I did:
There is another solution:
Beside trying to kill the session, also issue a resize command for the same datafile, in a different session, to the correct size.
Please be careful and don’t mess it up this time!
Oracle will allow the second session to complete! Voila! You didn’t run out of space and the import didn’t throw errors!
If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!
–Diana
Hello Diana,
Thank you for this article, it is very interesting.
Could you kindly share the scripts you used?
sessions.sql
sqltext.sql
pid.sql
Thanks in advance,
—
Abbas
Scripts can be found here as well:
https://dbaparadise.com/2017/01/three-dba-scripts-for-instant-answers-who-what-how/#1
One should think about the better alternative to KILL SESSION : DISCONNECT SESSION, it does all the stuff from database down to os 🙂
Great feedback! Thank you Olivier!
What happen to the first session? Sorry, I’m newbie. Thank you.
Hi Michael, the first session was terminated at one point, however it took some time.
[…] Today’s post was written based on your emails, comments and feedback for last week’s post Mistakes Happen – How To Cancel Alter Database Datafile Resize Command. […]
May be we can wait till the first command gets completed with error i.e No enough space left or unable to extend?
Same thing I have tested , but you will not not able to resize the datafile that contain data beyond the size you entred .Also oracle doesn’t check the size of your OS drive .
What happens if you press ‘ctrl+c’ while resizing the datafile? I was asking because we had an issue that the server rebooted automatically.
Bert, I haven’t experienced a server reboot with ctrl+c. From my experience, ctrl+c will not terminate that session, it will just be there, as if executing.
How to run ALTER DATABASE DATAFILE ‘/oradata/hrtst/dbf/undotbs01.dbf’ RESIZE 1021510M; using a pl/sql block.
have you tried execute immediate in the PLSQL block? I haven’t tested, so I don’t know if that would work or not.