Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_dml007.htm

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_dml007.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />

<meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" />
<meta name="date" content="2009-04-21T9:46:24Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Rolling Back Transactions" />
<meta name="relnum" content="11g Release 2 (11.2)" />
<meta name="partnum" content="E10766-01" />
<link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="tdddg_dml006.htm" title="Previous" type="text/html" />
<link rel="next" href="tdddg_dml008.htm" title="Next" type="text/html" />
<title>Rolling Back Transactions</title>
</head>
<body>
<div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr valign="bottom">
<td align="left"></td>
<td align="center"><a href="tdddg_dml006.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_dml008.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
<p><a id="BABDBCBI" name="BABDBCBI"></a><a id="TDDDG24200" name="TDDDG24200"></a></p>
<div class="sect1">
<h1>Rolling Back Transactions</h1>
<a name="BEGIN" id="BEGIN"></a>
<p><a id="sthref169" name="sthref169"></a><a id="sthref170" name="sthref170"></a><a id="sthref171" name="sthref171"></a><a id="sthref172" name="sthref172"></a>Rolling back a transaction undoes its changes. You can roll back the entire current transaction, or you can roll it back only to a specified savepoint.</p>
<p>To roll back the current transaction only to a specified savepoint, you must use the <a id="sthref173" name="sthref173"></a><code>ROLLBACK</code> statement with the <code>TO</code> <code>SAVEPOINT</code> clause.</p>
<p>To roll back the entire current transaction, use either the <code>ROLLBACK</code> statement without the <code>TO</code> <code>SAVEPOINT</code> clause, or (in the SQL&nbsp;Developer environment) the <a id="sthref174" name="sthref174"></a>Rollback Changes icon.</p>
<p><a id="sthref175" name="sthref175"></a><a id="sthref176" name="sthref176"></a>Rolling back the entire current transaction:</p>
<ul>
<li>
<p>Ends the transaction</p>
</li>
<li>
<p>Reverses all of its changes</p>
</li>
<li>
<p>Erases all of its savepoints</p>
</li>
<li>
<p>Releases any transaction locks</p>
</li>
</ul>
<p>Rolling back the current transaction only to the specified savepoint:</p>
<ul>
<li>
<p>Does not end the transaction</p>
</li>
<li>
<p>Reverses only the changes made after the specified savepoint</p>
</li>
<li>
<p>Erases only the savepoints set after the specified savepoint (excluding the specified savepoint itself)</p>
</li>
<li>
<p>Releases all table and row locks acquired after the specified savepoint</p>
<p>Other transactions that have requested access to rows locked after the specified savepoint must continue to wait until the transaction is either committed or rolled back. Other transactions that have not requested the rows can request and access the rows immediately.</p>
</li>
</ul>
<p>To see the effect of a rollback in SQL&nbsp;Developer, you might have to click the <a id="sthref177" name="sthref177"></a>Refresh icon.</p>
<p>As a result of the example in <a href="tdddg_dml002.htm#BABCCIEG">About the INSERT Statement</a>, the <code>REGIONS</code> table has a region called 'Middle East and Africa' and a region called 'Africa'. The following example corrects this problem (a very simple transaction) and checks the change, but then rolls back the transaction and then checks the rollback.</p>
<div class="example"><a id="CHDBAEDA" name="CHDBAEDA"></a><a id="TDDDG183" name="TDDDG183"></a>
<p class="titleinexample">Rolling Back an Entire Transaction</p>
<p>Before transaction:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT * FROM REGIONS
ORDER BY REGION_ID;
 
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa
</pre>
<p>Transaction (change table):</p>
<pre xml:space="preserve" class="oac_no_warn">
UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
1 row updated.
 
</pre>
<p>Check change:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT * FROM REGIONS
ORDER BY REGION_ID;
 
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         <span class="bold">4 Middle East</span>
         5 Africa
</pre>
<p>Roll back transaction:</p>
<pre xml:space="preserve" class="oac_no_warn">
<span class="bold">ROLLBACK;</span>
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
Rollback complete.
</pre>
<p>Check rollback:</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT * FROM REGIONS
ORDER BY REGION_ID;
 
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         <span class="bold">4 Middle East and Africa</span>
         5 Africa
</pre></div>

<div class="helpinfonotealso">
<h2>Related Topics</h2>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF01610','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a></p>
<p><a href="tdddg_dml.htm#CHDEBAED">About DML Statements and Transactions</a></p>
</div>
</div>


<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left"><span class="copyrightlogo">Copyright&nbsp;&copy;&nbsp;1996, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved.</span><br />
<a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td>
<td align="center"><a href="tdddg_dml006.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_dml008.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de