How to set Autocommit OFF
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to set Autocommit OFF

  1. #1
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Hi All,
    plz tell me how to set autocommit off. I guess by default auto commit is ON. I want to do the same for at database level. Any parameter for that??
    Thanks in advance...
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    AFAIK it is a SQL*Plus command and there is no such parameter that can be set in init.ora file.

    Sanjay

  3. #3
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    is there any way to set autocommit off??
    "Greatest Rewards come only with Greatest Commitments!"

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    From Sqlplus

    Code:
    set autocommit off;
    Sanjay

  5. #5
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Hi Sanjay,
    Thanks fo rthe fast reply...but my question was that can we do it at database level??
    Moreover even I set Autocommit off at the session level and I exit the session without commit, but still I can see those values which I deleted but did not commit. Any solution??
    "Greatest Rewards come only with Greatest Commitments!"

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Closing a user session does an explicit commit even if you set autocommit off. Similarly any DDL will also do an explicit commit. I don't think you can set autocommit off at database level.

    HTH
    Sanjay

  7. #7
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Originally posted by SANJAY_G
    Closing a user session does an explicit commit even if you set autocommit off. Similarly any DDL will also do an explicit commit.
    HTH
    Sanjay
    Then what is use of SET AUTOCOMMIT OFF. In what condition it works??
    "Greatest Rewards come only with Greatest Commitments!"

  8. #8
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    When you set autocommit on. The DMLs are commited immedialy after the statement finishes. But in case of autocommit off it is not commited unless you issue commit or exit from sqlplus.

    While exiting from Pro*C or JDBC you need to explicitly commit the transaction otherwise it will be rolled back.

    Sanjay

  9. #9
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Thanks Sanjay...it was a useful information...
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

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