Disable Autocommit for DDL statements

[gandolf989]

You can do an online move of the tables. Asktom has some answers oin how to do it.

asktom.oracle.com

It still seems like you are wasting your time. Did the tables and indexes shrink due to a recent purge? Do you expect that they will grow again? Well, if they grow again they will only reallocate all of the space that you are trying to save. If you need disk space buy another raid array. This will allow you spread out the data on more disks. You can always add another datafile.

[muck_isl]

I appreciate the previous comment, but this is what I need to do. Should I assume from the responses that there is no way to disable autocommit for DDL or no other Oracle tools that will allow a rollback of DDL statements.

It is unfortunate because I can do this with other RDBMS such as SQLServer and effectively.

Thanks to all who contributed; I do value the feedback.

[tamilselvan]

I appreciate the previous comment, but this is what I need to do. Should I assume from the responses that there is no way to disable autocommit for DDL or no other Oracle tools that will allow a rollback of DDL statements.

You are asking the Oracle Corp to rewrite the kernel code.

It is unfortunate because I can do this with other RDBMS such as SQLServer and effectively.

You are comparing apple with orange.

Tamil

[graham_o]

there's no way to disable 'autocommit' as you call it.

first step is to write the ddl script to undo the changes you're going to make.

if you write the undo script in the reverse order of the original script then you should be able to run it anyway. The steps that you're original script didn't get upto will just fail in the reverse script as it won't be able to perform the required action.

i.e. you try to drop a table that didn't get created or recreate a synonym that was never dropped.

I'm trying to think of a problem with this method, where you could actually do damage by trying to undo something that wasn't performed. off the top of my head I can't but you may find that undoing some of your ddl may be difficult. i.e. truncating a table (so make a copy in the original script).

sounds like a lot of work and testing anyway. put some thought into how you would reverse each step and go from there.

[slimdave]

How about taking a backup before the upgrade, then restoring if you need to rollback? Seems like you'd be doing the first part of that anyway.

[gandolf989]

How about taking a backup before the upgrade, then restoring if you need to rollback? Seems like you'd be doing the first part of that anyway.

Or just write the script so that it can be run more than once without corrupting anything.

[LKBrwn_DBA]

Or use OEM Change Manager -- It rollback anything.

[muck_isl]

Well, if OEM change manager will roll it back then there must be a way to do it with out autocommit - yes? Is there some way to identify the transaction information after each autocommitted DDL statement such that if I wanted to roll them back I could have Oracle roll-em back. I'd much prefer to rely upon Oracle than my own "undo" scripts. Thanks for the feedback!

[muck_isl]

......of course if it is has already been autocommitted you probably cannot roll it back at the transaction level. So probably a dumb question.

[slimdave]

Well, if OEM change manager will roll it back then there must be a way to do it with out autocommit - yes?

Not through rollback though, but by issuing other DDL to reverse the effects of the previous statements. Sometimes this is straightforward, like dropping an index you just created, but sometimes it is not. for example, if you drop a column of a table then the only way to get that data back is to have backed up the complete table (or the PK + that column) and then run some complex scripts to restore the data.