Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpii\tdpii_distdbs008.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"> <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" /> <title>Tutorial: Modifying Data in Multiple Oracle Databases</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" /> <meta name="date" content="2009-06-04T17:1:1Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="Tutorial: Modifying Data in Multiple Oracle Databases" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10703-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="tdpii_distdbs007.htm" title="Previous" type="text/html" /> <link rel="next" href="tdpii_distdbs009.htm" title="Next" type="text/html" /> <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> <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="tdpii_distdbs007.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpii_distdbs009.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="BABCCCGB" name="BABCCCGB"></a><a id="TDPII022" name="TDPII022"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h1>Tutorial: Modifying Data in Multiple Oracle Databases<a id="sthref61" name="sthref61"></a><a id="sthref62" name="sthref62"></a><a id="sthref63" name="sthref63"></a></h1> <a name="BEGIN" id="BEGIN"></a> <p>A <span class="bold">distributed transaction</span> includes one or more statements that, individually or as a group, modify data or the structure of database objects in two or more databases. In a synonym or in a statement that modifies data in a remote database, you can identify a database object by appending <code>@</code><code><span class="codeinlineitalic">dblink</span></code> to the end of its name. The <code><span class="codeinlineitalic">dblink</span></code> is a database link to the database that contains the remote database object.</p> <p>Statements that modify data in tables are called data manipulation language (DML) statements. Statements that modify the structure of database objects are called data definition language (DDL) statements. Both DML and DDL statements can be part of a distributed transaction.</p> <p>Meet the following conditions before running the distributed transaction in this topic:</p> <ul> <li> <p>Satisfy the prerequisites described in <a href="tdpii_distdbs006.htm#BABJDEED">"Preparing to Access and Modify Information in Multiple Oracle Databases"</a>.</p> </li> <li> <p>Create a database link from the local database to any remote database that contains a database object involved in the transaction. In the example in this topic, the <code>SYSTEM</code> user at the <code>ii1.example.com</code> database uses a database link that connects to the <code>SYSTEM</code> user at the <code>ii2.example.com</code> database. See <a href="tdpii_common_ii006.htm#BGBGIACD">"Tutorial: Creating a Database Link"</a> for information about creating such a database link.</p> </li> <li> <p>Ensure that the <code>hr</code> sample schema is installed on the local database, and the <code>oe</code> sample schema is installed on the remote database. These sample schemas are installed by default with Oracle Database.</p> </li> </ul> <p>This topic uses <code>ii1.example.com</code> and <code>ii2.example.com</code> as sample databases. You can substitute any two databases in your environment that meet these conditions.</p> <p>For this example, assume the following:</p> <ul> <li> <p>A company keeps its human resources information in the <code>hr</code> schema at the <code>ii1.example.com</code> database and its order entry information in the <code>ii2.example.com</code> database.</p> </li> <li> <p>The <code>employee_id</code> in the <code>hr.employees</code> table corresponds with the <code>sales_rep_id</code> in the <code>oe.orders</code> table.</p> </li> <li> <p>A sales representative has been promoted to the job of sales manager. The <code>employee_id</code> of this sales representative in the <code>hr.employees</code> table is <code>154</code>. The <code>job_id</code> and <code>manager_id</code> data for this employee must change in the <code>hr.employees</code> table.</p> </li> <li> <p>The current orders for the promoted sales representative must be transferred to a different sales representative. The <code>employee_id</code> of the sales representative who is now responsible for the orders is <code>148</code> in the <code>hr.employees</code> table. All of the orders in the <code>oe.orders</code> table currently assigned to <code>sales_rep_id</code> <code>154</code> must be changed to <code>sales_rep_id</code> <code>148</code>.</p> </li> </ul> <p>The company wants these changes to be committed in a single distributed transaction.</p> <p class="orderedlisttitle">To run a distributed transaction that changes data at both the ii1.example.com and ii2.example.com databases: </p> <ol> <li> <p>Create a synonym for the remote database object. In this example, create a synonym called <code>ord</code> in the <code>hr</code> schema that points to the <code>oe.orders</code> table at the <code>ii2.example.com</code> database. Step <a href="tdpii_distdbs007.htm#CIHCJDGB">1</a> in <a href="tdpii_distdbs007.htm#BABFJGFG">"Tutorial: Querying Multiple Oracle Databases"</a> contains instructions for creating this synonym.</p> </li> <li> <p>On a command line, open SQL*Plus and connect to the <code>ii1.example.com</code> database as the <code>SYSTEM</code> user.</p> <p>See <a href="topicid:ADMQS0361">Starting SQL*Plus and Connecting to the Database</a> for more information about starting SQL*Plus.</p> </li> <li> <p>Update the data at each database and commit your changes:</p> <pre xml:space="preserve" class="oac_no_warn"> UPDATE hr.employees SET job_id = 'SA_MAN', manager_id = 100 WHERE employee_id = 154; UPDATE hr.ord SET sales_rep_id = 148 WHERE sales_rep_id = 154; COMMIT; </pre></li> </ol> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="tdpii_intro2ii003.htm#CFHHAIGB">When to Access and Modify Information in Multiple Databases</a></p> <p><a href="tdpii_distdbs.htm#BABEFFBB">Accessing and Modifying Information in Multiple Databases</a></p> <p><a href="tdpii_distdbs007.htm#BABFJGFG">Tutorial: Querying Multiple Oracle Databases</a></p> <p><a href="tdpii_distdbs009.htm#BABFCFJJ">Tutorial: Running a Stored Procedure in a Remote Oracle Database</a></p> </div> </div> <!-- class="sect1" --> <!-- Start Footer --> <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 © 2007, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="tdpii_distdbs007.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpii_distdbs009.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de