Skip Headers
Oracle® Database 2 Day + Data Replication and Integration Guide
11g Release 2 (11.2)

Part Number E10703-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Accessing and Modifying Information in Multiple Databases

This chapter describes how to access and modify information in multiple Oracle and non-Oracle databases.

This chapter contains the following sections:

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

Distributed SQL enables applications and users to query or modify information in multiple databases with a single SQL statement. Because distributed SQL masks the physical location of your data, you can change the location of your data without changing your application. Distributed SQL includes the following: distributed queries (which access data) and distributed transactions (which modify data). In distributed transactions, the two-phase commit mechanism guarantees the integrity of your data by ensuring that all statements in a transaction either commit or roll back as a unit at each database involved in the distributed transaction.

When an application or user tries to commit a distributed transaction, the database to which the application or user is connected is called the global coordinator. The global coordinator completes the two-phase commit by initiating the following phases:

  • Prepare Phase: The global coordinator asks the other databases involved in the distributed transaction to confirm that they can either commit or roll back the transaction, even if there is a failure. If any database cannot complete the prepare phase, then the transaction is rolled back.

  • Commit Phase: If all of the other databases inform the global coordinator that they are prepared, then the global coordinator commits the transaction and asks all of the other databases to commit the transaction.

About Synonyms and Location Transparency

A synonym is a database object that acts as an alias for another database object. You can create both public and private synonyms. Every database user can access a public synonym. A private synonym is in the schema of a specific user, and only users who are granted access to the private synonym can use it.

In a distributed environment, synonyms can provide location transparency for database objects. A synonym hides the location of a database object from applications and users. If the database object must be moved or renamed, then you can redefine the synonym, and applications and users can continue to use the synonym without any modifications.

For example, suppose an application must access the hr.employees table at a remote database with the global name ii2.example.com. A database link exists for the remote database, and the name of the database link is ii2.example.com. In this case, you can create a synonym named employees in the hr schema that points to the table hr.employees@ii2.example.com. After the synonym is in place, the application at the local database can use hr.employees to access the remote table.

About Accessing and Modifying Information in Non-Oracle Databases

You can use distributed SQL to federate data not only in an Oracle database, but in non-Oracle databases as well. Oracle Database Gateway enables Oracle databases to access and modify data in several non-Oracle databases, including Sybase, DB2, Informix, Microsoft SQL Server, Ingres, and Teradata databases. This access is completely transparent to the end user. That is, you can issue the same SQL statements regardless of whether you are accessing data in an Oracle database or a non-Oracle database.

Note:

The capabilities of a specific Oracle Database Gateway are limited by the capabilities of the non-Oracle database being accessed. For information about the limitations of a specific Oracle Database Gateway, see the Oracle documentation for that specific gateway.

About Stored Procedures

To reduce network traffic when performing complex operations in a federated environment, you can use stored procedures. A procedure or function is a schema object that is run to solve a specific problem or perform a set of related tasks. Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not. Generally, you use a procedure to perform an action, and you use a function to compute a value. In this guide, the general term stored procedure includes both procedures and functions.

Oracle databases support stored procedures that are written in PL/SQL or Java, but this guide discusses only PL/SQL stored procedures. PL/SQL stored procedures consist of a set of SQL statements and other PL/SQL constructs that are grouped together and stored in the database. Stored procedures let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.

As with SQL statements, to run a stored procedure, you do not need to be aware of its physical location. Similarly, by using the appropriate Oracle Database Gateway, you can even call a stored procedure that is in a non-Oracle database. In this case, the gateway maps the PL/SQL calls to the non-Oracle database stored procedures.

See Also:

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.

Tutorial: 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:

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:

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

To run a distributed query that combines the information at the ii1.example.com and ii2.example.com 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.example.com database:

    1. Log in to Enterprise Manager as SYSTEM user.

    2. Go to the Database Home page for the ii1.example.com 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.example.com database link for the SYSTEM user.

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

    11. Click OK to create the synonym.

    Note:

    You can also use the CREATE SYNONYM SQL statement to create a synonym.
  2. On a command line, open SQL*Plus and connect to the ii1.example.com database as the SYSTEM user.

    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
    

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:

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:

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 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;
    

Tutorial: 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:

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:

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.example.com database as the SYSTEM user.

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

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

    exec hr.add_job_history@ii2.example.com(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.example.com database to see the inserted row:

    SELECT * FROM hr.job_history@ii2.example.com 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 several 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

Before you can query data, modify data, or run a stored procedure in a non-Oracle database, you must complete several tasks.

To begin to work with data in non-Oracle databases: 

  1. Install and configure Oracle Database Gateway software for each non-Oracle database. Oracle Database Gateway software can be installed on the computer system running an Oracle database, on the computer system running a non-Oracle database, or on a third computer system. For information about installing and configuring a specific Oracle Database Gateway, see the Oracle documentation for that specific gateway.

  2. Configure Oracle Net Services so that the Oracle database can communicate with the listener for Oracle Database Gateway that was configured in Step 1. See Oracle Database Net Services Administrator's Guide for instructions.

    After communication is established with Oracle Database Gateway, the Oracle database can communicate with the non-Oracle database. An Oracle database can use Heterogeneous Services to communicate with Oracle Database Gateway. See Oracle Database Heterogeneous Connectivity User's Guide for information about configuration options for Heterogeneous Services.

  3. Create a database link to the non-Oracle database. The database link must be created with an explicit CONNECT TO clause. After you complete Step 1 and 2, you can create the database link in the same way that you would to connect to an Oracle database. See "Tutorial: Creating a Database Link" for instructions.

After completing these steps, you can access data and procedures in the non-Oracle database transparently. To work with the non-Oracle database, follow the instructions in these topics and specify database objects in the non-Oracle database:

Best Practices for Working with Non-Oracle Databases

Oracle Database Gateway performance is affected by several factors, including network speed, available memory, amount of data being transferred from one database to the other, and the number of concurrent sessions. Some of these factors can be adjusted for better performance.

You can achieve better performance by following these best practices:

Reduce Post Processing

Performance can be affected negatively if there is a large amount of post processing. Ensure that as much of each SQL statement as possible is processed on the non-Oracle database to achieve better performance. If parts of the WHERE clause are missing from the SQL that is sent to the non-Oracle database or if joins are split, then the query will be post processed.

Follow these best practices to reduce post processing:

  • Avoid SQL functions in the WHERE clause, if possible. SQL functions in WHERE clauses affect the performance of the gateway. If you are using Oracle functions that do not have an equivalent in the non-Oracle database, then the gateway compensates for it. The data is retrieved from the non-Oracle database and the function is applied on the Oracle database.

  • Use hints to improve the query plan. Look at the gateway trace file or explain plan to determine the SQL being sent to the non-Oracle database. For example, if the SQL statement includes joins of tables on the non-Oracle database, and they are being post processed, then you can use hints to cause the joins to be performed on the non-Oracle database.

Tune the Non-Oracle Database

Performance can be affected negatively if the optimizer does not have enough information to generate an optimal plan. The Oracle optimizer uses table and index statistics of the non-Oracle database to determine the most optimal path to access the data in the non-Oracle database. If this information is missing or inaccurate, then the access path is not optimal. Defining indexes on the non-Oracle database improves the performance of the gateway. See Oracle Database 2 Day + Performance Tuning Guide for more information about the Oracle optimizer.

Set the Relevant Initialization Parameters

Customizing the following initialization parameters can improve performance:

  • HS_RPC_FETCH_SIZE

  • HS_FDS_FETCH_ROWS

  • HS_LANGUAGE

For example if you are sure that the data you are accessing in the non-Oracle database is in the same character set as the one used by the Oracle database, then set the HS_LANGUAGE initialization parameter to the character set of the Oracle database.

Check the Location of the Oracle Database Gateway Installation

The location of the Oracle Database Gateway installation might affect performance. For example, if CPU is a constraint, then do not install the Oracle Database Gateway on the same computer systems where Oracle databases are running.

Ensure Adequate Memory

Ensure that there is enough memory on the computer system where the Oracle Database Gateway is running. There are several factors that affect memory requirements. These include the SQL statement being processed, the number of concurrent sessions, the number of open cursors, and the number of columns in the table being accessed.

Consider Case Differences

When you are working with non-Oracle databases, remember that an Oracle database defaults characters to uppercase unless you surround identifiers with double quotation marks. For example, to refer to the Sybase table named emp, enter the name with double quotation marks, as in the following example:

SELECT * FROM "emp"@SYBS;

However, to refer to the Sybase table called emp owned by user Smith from an Oracle application, enter the following:

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

If the Sybase table named emp is owned by SMITH, a table owner name in uppercase letters, then you can enter the owner name without double quotation marks, as in the following examples:

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

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

See Also: