Solved

Commit/Rollback

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle 10G 5 48
Number Format 1 44
unable to get sorting resultset 15 45
PL/SQL Search for multiple strings 5 21
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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

705 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

21 Experts available now in Live!

Get 1:1 Help Now