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, and (2) a distributed update.
Still True in Oracle 23c:
Oracle 23c continues to support database links (`DBLINK`) that allow a local database to access objects in a remote database via SQL statements.

• Simple distributed queries (SELECT * FROM remote_table@remote_db) still work.
• Distributed updates (INSERT, UPDATE, DELETE on remote objects) are still supported through two-phase commit (2PC) mechanisms.

🧠 Enhancements in Oracle 23c
Here’s what’s *new or improved* in Oracle 23c, especially in cloud/network topologies:

1. Auto CDB-to-CDB links
   • You can now more easily link PDBs across CDBs, even in multi-tenant cloud configurations.
2. Secure communication
   • Oracle 23c encourages TLS-encrypted communication and supports wallet-based authentication for DB Links in cloud environments.
3. Globally Distributed Transactions
   • You can use Application Continuity and Transaction Guard in conjunction with distributed transactions for fault resilience.
4. Cross-Region Support in Oracle Cloud
   • With the right TNS configuration and security policies, you can have cross-region DB links in OCI, supporting distributed queries and updates across Oracle Cloud regions.
5. Oracle Cloud Autonomous Database (limited)
   • If using Oracle Autonomous DBs, note that CREATE DATABASE LINK is not always available—instead, Oracle recommends using REST-based APIs or Oracle GoldenGate for such use cases.

🛠️ Example (Still Works in 23c):

SELECT * FROM employees@hrdb_link;

UPDATE employees@hrdb_link
SET salary = salary * 1.1
WHERE department_id = 10;

🔒 Best Practice for Oracle 23c Cloud:

• Use CREATE DATABASE LINK ... IDENTIFIED BY VALUES with wallet-based credentials.
• Ensure Oracle Net configuration (tnsnames.ora / Easy Connect) is correct in multi-CDB setups.
• Monitor and log distributed transaction behavior using DBA_2PC_PENDING, V$GLOBAL_TRANSACTION, and Oracle's new event-driven diagnostics.

The following sentence still hold true for "Oracle network topology" in their cloud platform Oracle 23c.
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

Now all SQL references to the ITEM table will transparently reference item@rochester. The next lesson concludes this module.

[1]Distributed query: Within an Oracle Network Topology, a distributed query is a single SQL statement that accesses data from multiple distinct Oracle databases across the network. This allows users to retrieve and combine information residing in different database instances as if they were a single, unified data source.