Data can be replicated between a non-Oracle system and Oracle Database using materialized views.
Note:
There is another means of replicating information between Oracle and non-Oracle databases called Oracle Streams.
For information about using Oracle Streams, see Oracle Streams Concepts and Administration.
Materialized views instantiate data captured from tables at the non-Oracle master site at a particular time. This instant is defined by a refresh operation, which copies this data to Oracle Database and synchronizes the copy on the Oracle system with the master copy on the non-Oracle system. The materialized data is then available as a view on Oracle Database.
Replication facilities provide mechanisms to schedule refreshes and to collect materialized views into replication groups to facilitate their administration. Refresh groups permit refreshing multiple materialized views as if they were a single object.
Heterogeneous replication support is necessarily limited to a subset of the full Oracle-to-Oracle replication functionality:
Only the non-Oracle system can be the primary site. This is because materialized views can be created only on Oracle Database.
Materialized views must use a complete refresh. This is because fast refresh would require Oracle-specific functionality in the non-Oracle system.
Not all types of materialized views can be created to reference tables on a non-Oracle system. Primary key and subquery materialized views are supported, but ROWID and OBJECT ID materialized views are not supported. This is because there is no SQL standard for the format and contents of ROWID, and non-Oracle systems do not implement Oracle objects.
Other restrictions apply to any access to non-Oracle data through Oracle's Heterogeneous Services facilities. The most important of these are:
Non-Oracle data types in table columns mapped to a fixed view must be compatible with (that is, have a mapping to or from) Oracle data types. This is usually true for data types defined by ANSI SQL standards.
A subquery materialized view may not be able to use language features restricted by individual non-Oracle systems. In many cases, Heterogeneous Services supports such language features by processing queries within Oracle Database. Occasionally the non-Oracle systems impose limitations that cannot be detected until Heterogeneous Services attempts to execute the query.
The following examples illustrate basic setup and use of three materialized views to replicate data from a non-Oracle system to an Oracle data store.
Note:
For the following examples, remote_db refers to the non-Oracle system that you are accessing from Oracle Database.
Modify these examples for your environment. Do not try to execute them as they are written.