Databases Reference
In-Depth Information
Distributed Queries and Transactions
Data within an organization is often spread among multiple databases for reasons of
both capacity and organizational responsibility. Users may want to query this distributed
data or update it as if it existed within a single database.
Oracle first introduced distributed databases in response to the requirements for ac‐
cessing data on multiple platforms in the early 1980s.
Distributed queries
can retrieve
data from multiple databases.
Distributed transactions
can insert, update, or delete data
on distributed databases. Oracle's two-phase commit mechanism guarantees that all the
database servers that are part of a transaction will either commit or roll back the trans‐
action. Background recovery processes can ensure database consistency in the event of
system interruption during distributed transactions. Once the failed system comes back
online, the same process will complete the distributed transactions.
Distributed transactions can also be implemented using popular transaction monitors
(TPs) such as Tuxedo that interact with Oracle via XA, an industry standard (X/Open)
interface.
Heterogeneous Services
Heterogeneous Services allow non-Oracle data and services to be accessed from an
Oracle Database through generic connectivity via ODBC and OLE-DB, which are in‐
cluded with the database.
Optional Transparent Gateways use agents specifically tailored for a variety of target
systems. Transparent Gateways allow users to submit Oracle SQL statements to a non-
Oracle distributed database source and have them automatically translated into the SQL
dialect of the non-Oracle source system, which remains transparent to the user. Gate‐
ways are available for Sybase, Microsoft SQL Server, DRDA, Informix, Teradata, APPC,
and WebSphere MQ.
In addition to providing underlying SQL services, Heterogeneous Services provide
transaction services utilizing Oracle's two-phase commit with non-Oracle databases
and procedural services that call third-generation language routines on non-Oracle
systems. Users interact with the Oracle Database as if all objects are stored in the Oracle
Database, and Heterogeneous Services handle the transparent interaction with the for‐
eign database on the user's behalf. Oracle 12c includes a new feature called SQL Trans‐
lation, which allows the Oracle Database to dynamically translate SQL from a different
database. This feature makes it easier to migrate data to an Oracle Database without the
need for excessive rewrites of SQL in your applications. The feature is described in
Chapter 4
.
