Page 77
A distributed database is one logical database that is implemented as two or more physical databases on either the same machine or separate machines thousands of miles away. The system's designers decide where the tables should physically reside.
Each physical database has its own instance and sets of files, and the machines on which the databases reside are connected over a network. The location of tables can be made transparent to the application using database links and synonyms.
Oracle enables a transaction and even a single statement to access tables on two or more distributed databases. This does not necessitate any more coding by the application developers.
A distributed transaction is a transaction that modifies tables on more than one database and then expects all the changes to be committed. If there is any kind of failure, all the changes on all the databases are rolled back. A distributed transaction can involve many Oracle databases and only one non-Oracle database. The Oracle two-phase commit mechanism controls the synchronization of commits across all databases and can automatically roll back changes on all the databases if any kind of failure should occur. The RECO background process synchronizes this operation.
In addition to this functionality, Oracle also provides the capability to replicate tables from one database to others. This is called creating a snapshot of the table.
You create a snapshot with the CREATE SNAPSHOT command on the database where you want to have the copy of the data. The Oracle RDBMS software automatically sends down any changes made to the master copy of the table to each of the snapshot copies at user -defined intervals, without any manual intervention.
The snapshot mechanism enables you to make updates to the snapshot copy of the table, in which case the changes are sent from the copy table back to the master table.
Chapter 55, "Networking," discusses distributed databases in greater detail.
Oracle's national language support (NLS) enables users to use the database in their own languages. It provides the following functions:
Page 78
You can add support for new languages using the NLS*WorkBench product, which essentially maintains translation tables for interpreting input from the user and for displaying output.
When it comes to delivering application systems in different languages, the most important part of the user interface is the different prompts, boilerplate , and messages from the application. Currently, the application developers themselves define how the boilerplate, prompts, and messages from the application system change from one language to another. Oracle is working on a translation product to make this task easier.
A data warehouse is a database targeted for decision support and will include very large amounts of historical, summarized, and consolidated data (gigabytes to terabytes in size ). Much of the activity on the database will be query-oriented involving large and complex joins across many tables. A major consideration will be the I/O and CPU load required to perform these operations in an acceptable time frame.
Oracle8 offers the following major options for a data warehouse application:
These options, once properly set up and configured, will ensure that the time taken to load, query and execute operations on large data is acceptable.
Page 79
This section brings together major parts of the Oracle architecture and follows the steps that a typical SQL statement might go through to be executed. We use a simple scenario with both the Oracle SQL*Plus tool and the Oracle database server machine on a UNIX box without any networking involved. Using a single task configuration, the Oracle instance has just started.
The following shows some of the steps involved in executing SQL statements.