3 Accessing and Modifying Information in Multiple Databases

About Accessing and Modifying Information in Multiple Databases

While connected to an Oracle database, you can access and modify information in other Oracle databases and in non-Oracle databases. When information in two or more databases appears to be in a single database, it is called federation. Federation leaves information in its original location, where it is maintained and updated. Multiple data sources appear to be integrated into a single virtual database so that different kinds of databases are presented in one consolidated view. A federated configuration can make all of your databases look like one virtual database to applications and end users, thereby reducing some of the complexity of the distributed system.

The following topics contain more information about accessing and modifying information in multiple databases:

• About Distributed SQL

• About Synonyms and Location Transparency

• About Accessing and Modifying Information in Non-Oracle Databases

• About Stored Procedures

Preparing to Access and Modify Information in Multiple Oracle Databases

This topic describes actions that are required to prepare your databases to access and modify information at other databases.

To prepare to access and modify information in multiple databases:

1. Set the GLOBAL_NAMES initialization parameter to TRUE at each Oracle database in the distributed environment. See "Setting the GLOBAL_NAMES Initialization Parameter to TRUE" for instructions.

2. Configure network connectivity so that the databases can communicate with each other. See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

Example: Querying Multiple Oracle Databases

A distributed query accesses information in two or more databases. In a synonym or in a SELECT statement, you can identify a remote table, view, or materialized view by appending @dblink to the end of its name. The dblink is a database link to the database that contains the remote database object.

Meet the following conditions before running the distributed query 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 query. In the example in this topic, the SYSTEM user at the ii1.net database uses a database link that connects to the SYSTEM user at the ii2.net database. See "Example: 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.net and ii2.net 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.net database and its order entry information in the ii2.net database.

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

• A manager has a question about an order and wants to contact the sales representative for the order.

In this case, the contact information for the sales representative is in the hr.employees table in the ii1.net database, and the order information is in the oe.orders table in the ii2.net database.

To run a distributed query that combines the information at the ii1.net and ii2.net databases to show the contact information for the sales representative:

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.net database:

   1. Log in to Enterprise Manager as SYSTEM user.

   2. Go to the Database Home page for the ii1.net database instance.

   3. Click Schema to open the Schema subpage.

   4. Click Synonyms in the Database Objects section.

   5. On the Synonyms page, click Create.

   6. On the Create Synonym page, enter ord in the Name field in the General section.

   7. With Schema selected for the Type, enter hr in the Schema field or click the flashlight icon to select the hr schema.

   8. In the Database section, select Remote.

   9. Click the flashlight icon for the Service Name field to select the ii2.net database link for the SYSTEM user.

   10. In the As Alias For section, enter oe.orders.

   11. Click OK to create the synonym.

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

   sqlplus system/user-password@ii1.net
   

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

3. Run the following query:

   COLUMN FIRST_NAME HEADING 'First Name' FORMAT A20
   COLUMN LAST_NAME HEADING 'Last Name' FORMAT A20
   COLUMN PHONE_NUMBER HEADING 'Phone Number' FORMAT A20
   
   SELECT e.first_name, e.last_name, e.phone_number
     FROM hr.employees e, hr.ord o
     WHERE o.order_id    = 2456 AND
           e.employee_id = o.sales_rep_id;
   

   The output will be similar to the following:

   First Name           Last Name            Phone Number
   -------------------- -------------------- --------------------
   Danielle             Greene               011.44.1346.229268
   

Example: 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.net database uses a database link that connects to the SYSTEM user at the ii2.net database. See "Example: 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.net and ii2.net 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.net database and its order entry information in the ii2.net 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.net and ii2.net 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.net database. Step 1 in "Example: Querying Multiple Oracle Databases" contains instructions for creating this synonym.

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

   sqlplus system/user-password@ii1.net
   

   See Oracle Database 2 Day DBA 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;
   

Example: Running a Stored Procedure in a Remote Oracle Database

A remote procedure call (RPC) runs a procedure or function at a remote database. An RPC performs any work defined in the remote procedure. To run a remote procedure or function, you can identify the remote procedure or function by appending @dblink to the end of its name. The dblink is a database link to the database that contains the remote procedure or function. You can also create a synonym that points to the remote procedure or function.

Meet the following conditions before performing the sample RPC 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 procedure or function that is being called. In the example in this topic, the SYSTEM user at the ii1.net database uses a database link that connects to the SYSTEM user at the ii2.net database. See "Example: Creating a Database Link" for information about creating such a database link.

• Ensure that the hr sample schema is installed on the remote database. The hr sample schema is installed by default with Oracle Database.

This topic uses ii1.net and ii2.net 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 ii2.net database.

• The hr schema does not exist at the local ii1.net database.

• When an employee leaves the company, a procedure called add_job_history in the hr schema inserts a row into the hr.job_history table. The row contains information about the history of the employee with the company.

• The employee with an employee_id of 127 is leaving the company.

• You want to connect to the ii1.net database and run the add_job_history procedure at the ii2.net database to record the job history for employee 127.

To run an RPC to record the job history of the employee:

1. On a command line, open SQL*Plus and connect to the ii1.net database as the SYSTEM user:

   sqlplus system/user-password@ii1.net
   

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Run the add_job_history procedure at the ii2.net database to record the job history of the employee:

   exec hr.add_job_history@ii2.net(127,'14-JAN-99','26-JUN-06','ST_CLERK',50);
   

3. Commit the changes:

   COMMIT;
   

   This step is not necessary if the remote procedure commits.

4. Optionally, query the hr.job_history table at the ii2.net database to see the inserted row:

   SELECT * FROM hr.job_history@ii2.net ORDER BY employee_id;
   

   The output will be similar to the following:

   EMPLOYEE_ID START_DAT END_DATE  JOB_ID     DEPARTMENT_ID
   ----------- --------- --------- ---------- -------------
           101 21-SEP-89 27-OCT-93 AC_ACCOUNT           110
           101 28-OCT-93 15-MAR-97 AC_MGR               110
           102 13-JAN-93 24-JUL-98 IT_PROG               60
           114 24-MAR-98 31-DEC-99 ST_CLERK              50
           122 01-JAN-99 31-DEC-99 ST_CLERK              50
           127 14-JAN-99 26-JUN-06 ST_CLERK              50
           176 24-MAR-98 31-DEC-98 SA_REP                80
           176 01-JAN-99 31-DEC-99 SA_MAN                80
           200 17-SEP-87 17-JUN-93 AD_ASST               90
           200 01-JUL-94 31-DEC-98 AC_ACCOUNT            90
           201 17-FEB-96 19-DEC-99 MK_REP                20
   

   Notice that the job history of the employee with an employee_id of 127 is recorded in the table.

Working with Data in Non-Oracle Databases

Oracle Database Gateway enables Oracle databases to access and modify data in a number of non-Oracle databases, including Sybase, DB2, Informix, Microsoft SQL Server, Ingres, and Teradata databases. For the best performance and usability, you should follow established best practices for working with non-Oracle databases.

This section contains these topics:

• Configuring Oracle Databases to Work with Non-Oracle Databases

• Best Practices for Working with Non-Oracle Databases

SELECT * FROM "emp"@SYBS;

SELECT * FROM "Smith"."emp"@SYBS;

SQL> SELECT * FROM SMITH."emp"@SYBS;
 
SQL> SELECT * FROM smith."emp"@SYBS;

SQL> SELECT * FROM SmItH."emp"@SYBS;