Disable Autocommit for DDL statements

I need some help with a database script I want to provide to some of my constituents that issues multiple Oracle DDL statements. I need these DDL statements to be treated as a single transaction such that if one of them fails it will automatically rollback the changes to the state prior to running the script. The problem I am facing is all Oracle DDL statements are autocommited treating each DDL statement as a single transaction.

I have heard there is a tool called Flashback that might allow rollback, but as I understand it this is an option and not available on all Oracle installations. So, this is not a practical option for me.

What I need is to be able to disable Oracle’s autocommit facility while running the DDL statements, but I don’t see a way to do this.

Is there a way to combine multiple Oracle DDL statements into a single script such that they are treated as a single transaction and can be rolled back?

Appreciate any advice!

ddl are auto commit, you cant turn that off

You can however write code that will execute each ddl statement and rollback those statements if there is an error. But depending on the error, and the ddl, you might not get everything rolled back.

What types of ddl statements are you planning on running?

Users/Developers should NOT be issuing DDL on application schemas in the first place!
Developers/Users can issue DDL on their own accounts, and there you could care less what they are doing.
:rolleyes:

Quote:

---

Originally Posted by LKBrwn_DBA

Users/Developers should NOT be issuing DDL on application schemas in the first place!
Developers/Users can issue DDL on their own accounts, and there you could care less what they are doing.
:rolleyes:

---

There are datawarehouse programs that let users create user defined tables. Then they can use an ETL tool to populate them with data. I worked on one such application. Although in that case it created scripts which were then run in SQL Plus.

and he never said he was a user or developer, might be developing an upgrade script or something

Yes - this is along the lines of an upgrade script. I just want to have some assurance that if it fails it will rollback to the previous state. As it stands, if I have muliple DDL statements and if there is a failure I will be leaving things in an indeterminate state.

Appreciate the feedback from the forum.

well if its done in sqlplus make it quite on error so you know exactly how far it got and start from there

I would prefer to be able to run the DDL script without taking peoples Oracle database offline. I'm wondering if something such as "online table redefinition" would help me. I have no experience with this (admittedly not very Oracel knowlwdgable).

Anyone familiar with Online Table Redifinitions?

Is this something that could allow a script to run DDL statements while the database was available and provide a means of recover should one of the DDL statements fail?

Thanks again for the response!!

so you want to making schema changes while people are still using it

uh huh, sounds like a great idea

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.

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.

Quote:

---

Originally Posted by 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.

---

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

Quote:

---

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

---

You are comparing apple with orange.

Tamil

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.

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.

Quote:

---

Originally Posted by 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.

---

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

Or use OEM Change Manager -- It rollback anything. ;)

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!

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

Quote:

---

Originally Posted by muck_isl

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.