[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Commit/Rollback

Posted on 2001-07-20
5
Medium Priority
?
2,018 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

656 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