Make Connection using Oracle Database Link

Once the database link has been created, you can reference it in the SQL. There are two types of transactions you can accomplish via database links:

1. a simple distributed query ^[1], and
2. a distributed update.

For example:

SELECT order_number, item_number
FROM
   order@raleigh o,
   Item@rochester i
WHERE
o.item_number = i.item_number;

A distributed update modifies data on two or more nodes. A distributed update is possible using a PL/SQL subprogram unit, such as a procedure or trigger, that includes two or more remote updates accessing data on different nodes. In the example below, I have decremented the inventory at the Rochester plant before adding the order to the database at Raleigh.

BEGIN 
  UPDATE item@rochester 
    SET quantity_on_hand = quantity_on_hand - 1 
    WHERE item_id = 12345;
 
  INSERT INTO order@raleigh 
    VALUES (‘order_number’,’item_number);
END;

This ensures that the inventory adjustment and the order entry happen as a single transaction. The RECO Oracle process on the remote nodes is used to ensure that both nodes complete their piece of the transaction before the whole distributed transaction is committed to each database. This is known as a two-phase commit. As the name implies, the two-phase commit works in two distinct steps. The first step is the prepare phase, where the rows are prepared to be committed to the remote databases. The second phase, called the commit phase, is where the entire distributed transaction is either committed or rolled back as a single unit of work. The following figure illustrates the process.

Remember, location transparency is easily achieved by hiding the service name with a public synonym. For example, if you do not want your programmers to be concerned that your inventory database is in Rochester, you could create the following synonym:

CREATE PUBLIC SYNONYM item for item@rochester

[1]Distributed query: A distributed query retrieves information from two or more nodes.