Tutorial: Modifying Data in Multiple Oracle Databases

A distributed transaction 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 @dblink to the end of its name. The dblink is a database link to the database that contains the remote database object.

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.

Meet the following conditions before running the distributed transaction in this topic:

• Satisfy the prerequisites described in "Preparing to Access and Modify Information in Multiple Oracle Databases".

• 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 SYSTEM user at the ii1.example.com database uses a database link that connects to the SYSTEM user at the ii2.example.com database. See "Tutorial: Creating a Database Link" for information about creating such a database link.

• Ensure that the hr sample schema is installed on the local database, and the oe sample schema is installed on the remote database. These sample schemas are installed by default with Oracle Database.

This topic uses ii1.example.com and ii2.example.com as sample databases. You can substitute any two databases in your environment that meet these conditions.

For this example, assume the following:

• A company keeps its human resources information in the hr schema at the ii1.example.com database and its order entry information in the ii2.example.com database.

• The employee_id in the hr.employees table corresponds with the sales_rep_id in the oe.orders table.

• A sales representative has been promoted to the job of sales manager. The employee_id of this sales representative in the hr.employees table is 154. The job_id and manager_id data for this employee must change in the hr.employees table.

• The current orders for the promoted sales representative must be transferred to a different sales representative. The employee_id of the sales representative who is now responsible for the orders is 148 in the hr.employees table. All of the orders in the oe.orders table currently assigned to sales_rep_id 154 must be changed to sales_rep_id 148.

The company wants these changes to be committed in a single distributed transaction.

To run a distributed transaction that changes data at both the ii1.example.com and ii2.example.com databases: 

1. Create a synonym for the remote database object. In this example, create a synonym called ord in the hr schema that points to the oe.orders table at the ii2.example.com database. Step 1 in "Tutorial: Querying Multiple Oracle Databases" contains instructions for creating this synonym.

2. On a command line, open SQL*Plus and connect to the ii1.example.com database as the SYSTEM user.

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

3. Update the data at each database and commit your changes:

   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;