Home > tips > How to cancel alter database datafile resize command

How to cancel alter database datafile resize command


If you erroneously give alter database resize command, such as giving 33,884,000M  instead of 3,388,400MMB. Which may cause you to end up with disk space.

You can kill the sql from another session, but also you could give true resize value as well.

Here what happens, in alert log file, from real world scenario:

Thu Mar 03 04:28:12 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 33884000M
Thu Mar 03 04:31:59 2011
Immediate Kill Session#: 600, Serial#: 53111
Immediate Kill Session: sess: 7000007726370c8  OS pid: 52691066
Thu Mar 03 04:32:15 2011
Immediate Kill Session#: 866, Serial#: 41187
Immediate Kill Session: sess: 7000007726ae1c8  OS pid: 34406652
Thu Mar 03 04:34:31 2011
NOTE: deferred map free for map id 116809
Error occured while spawning process O004; error = 601
Thu Mar 03 04:36:27 2011
Immediate Kill Session#: 1133, Serial#: 30931
Immediate Kill Session: sess: 7000007687084b0  OS pid: 63242370
Thu Mar 03 04:39:57 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388400M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388400M
...
Thu Mar 03 04:40:14 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388500M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388500M
...
Thu Mar 03 04:40:31 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3389500M
Thu Mar 03 04:42:35 2011
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb1/trace/mydb1_ora_65994754.trc  (incident=225358):
ORA-00240: control file enqueue held for more than 120 seconds
Incident details in: /u01/app/oracle/diag/rdbms/mydb/mydb1/incident/incdir_225358/mydb1_ora_65994754_i225358.trc
Thu Mar 03 04:42:40 2011
Dumping diagnostic data in directory=[cdmp_20111103044240], requested by (instance=1, osid=65994754), summary=[incident=225358].
Thu Mar 03 04:47:25 2011
minact-scn: useg scan erroring out with error e:12751
Thu Mar 03 04:47:25 2011
Sweep [inc][225358]: completed
Sweep [inc2][225358]: completed
Thu Mar 03 04:49:36 2011
Error occured while spawning process O007; error = 601
Thu Mar 03 04:52:01 2011
Completed: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3389500M
Advertisements
Categories: tips Tags: , , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: