Commit/Rollback

Dear Experts,
             Can you list me all transactions and sessions specifying whether commit is required or not i.e. autocommit? For ex. commit is required for DML but not for DDL statements. Does this apply for all other objects apart from a table. What about grant and revoke statements. Does no autocommit always mean that there can be a rollback. Please be elaborate in your answer.
LVL 17
k_murli_krishnaAsked:
Who is Participating?
 
noriegaaConnect With a Mentor Commented:
While it is true that commit is not required for DDLs, I found situations where a session may lock a table and you cannot perform a DDL.  For example, if you wish to widen a column, and another user -in another session- is pending to commit a row on that table, you cannot perform this action  until that user formally commits (Oracle8).

Commit is required for all transactions using DML's commands such as insert, update, and delete, including those similar changes generated by triggers.  "Commit work;" is appropriate at that point.

If you set autocommit on in your session, like from SQL*Plus, every transaction will be automatically committed and cannot be rolled back.


If you have not committed yet, and autocommit is set to off, you can always rollback or rollback work, or to a specific savepoint.

For further details, refer to commit, rollback and savepoint in your Oracle SQL/PL/SQL Reference and Developer's manual.

0
 
juneja_aCommented:
Hello
     You are right commit is not required for ddl and it is right for any ddl whcih can be realted to any objects
may be table,view,package,tablespace,rollback. also on grant and revoke statements. for these there is no rollback thing. for these statements it doesn't matter ehat is the status of auto commit. you can't rollback any of these statements with a rollback cammand.  Autocommit  or no autocommit is only applicable for DML. ie. update.insert.delete statements. if ur setting for auto commit is false then you can rollback these DML statements otherwise not.

Amit
0
 
k_murli_krishnaAuthor Commented:
Thanx juneja a and noriegaa. I am sorry i can grade/rate only one expert.
0
 
RMZCommented:
hi
note:
any DML commands need commit (means u can make rollback)
DDL,DCL commands not need commit
but when u run DML commands then  run DDL,DCL commands
the commit occur
---rmz---
0
 
k_murli_krishnaAuthor Commented:
RMZ, thanx for the useful tip
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.