Disable Autocommit for DDL statements
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Disable Autocommit for DDL statements

  1. #1
    Join Date
    Mar 2006
    Posts
    7

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    ddl are auto commit, you cant turn that off

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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?
    this space intentionally left blank

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Cool


    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.


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.


    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.
    this space intentionally left blank

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and he never said he was a user or developer, might be developing an upgrade script or something

  7. #7
    Join Date
    Mar 2006
    Posts
    7
    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.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well if its done in sqlplus make it quite on error so you know exactly how far it got and start from there

  9. #9
    Join Date
    Mar 2006
    Posts
    7
    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!!

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    so you want to making schema changes while people are still using it

    uh huh, sounds like a great idea

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width