Disable Autocommit for DDL statements

[muck_isl]

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!

[davey23uk]

ddl are auto commit, you cant turn that off

[gandolf989]

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?

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

[gandolf989]

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.

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.

[davey23uk]

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

[muck_isl]

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.

[davey23uk]

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

[muck_isl]

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!!

[davey23uk]

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

uh huh, sounds like a great idea