Solved

Commit/Rollback

Posted on 2001-07-20
5
2,006 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 30 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Array of Key/Value Pairs as input to Oracle function 10 53
Outer Query not returning data - SQL HELP 16 50
report returning null 21 80
execute immediate plsql block 5 34
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now