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