?
Solved

Commit/Rollback

Posted on 2001-07-20
5
Medium Priority
?
2,020 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:k_murli_krishna
5 Comments
 

Expert Comment

by:juneja_a
ID: 6303077
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
 
LVL 2

Accepted Solution

by:
noriegaa earned 120 total points
ID: 6303147
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
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 6305699
Thanx juneja a and noriegaa. I am sorry i can grade/rate only one expert.
0
 
LVL 2

Expert Comment

by:RMZ
ID: 6305723
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
 
LVL 17

Author Comment

by:k_murli_krishna
ID: 6343826
RMZ, thanx for the useful tip
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question