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.

alter database - image 1

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:

alter database - image 2

alter database - image 3

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!

alter database - image 4

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


11 thoughts on “Mistakes Happen – How To Cancel Alter Database Datafile Resize Command

  1. 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

  2. One should think about the better alternative to KILL SESSION : DISCONNECT SESSION, it does all the stuff from database down to os 🙂

  3. 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 .

  4. What happens if you press ‘ctrl+c’ while resizing the datafile? I was asking because we had an issue that the server rebooted automatically.

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *