Oracle Streams allows data propagation between Oracle Databases (homogeneous) and between Oracle and non-Oracle databases (heterogeneous environment).
Oracle Streams can be used for:
- Message Queuing
- Loading data into a Data Warehouse
- Event Notification
- Data Protection
Oracle Streams was first shipped with Oracle 9i release 9.2.
Oracle Streams is only available with the Oracle Database Enterprise Edition.
Oracle 11gR2 is the last release supporting Streams. Oracle now recommends to use Golden Gate.
 Components of Oracle Streams
Oracle Streams consists of the following components:
Database changes (DDL and DML) are captured from the redo logs and packaged into Logical Change Records or LCRs. The LCRs are then moved into the Staging environment. Data and events may be changed or formatted by a predefined set of rules before they are packaged into an LCR.
Your database must be in ARCHIVE log mode.
alter database archivelog;
Additionally, you may also need to enable supplemental logging for the database to ensure all changes are captured in the log files:
alter database add supplemental log data (primary key, unique index) columns;
You also need to enable force logging to ensure NOLOGGING operations are considered.
alter database force logging;
Create a queue table - used to queue captured changes:
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'strm_scott_qtable', storage_clause => 'storage (initial 10M next 10M maxextents unlimited)', queue_name => 'strm_scott_queue', queue_user => 'scott'); END; /
LCRs are stored in the Staging environment until a subscriber picks them up to be used or consumed. The subscriber may be another staging environment or a user application.
During Consumption, LCRs are picked up and applied to a database. Consumption allows the LCR to be modified before it is applied to the database.
 Some Streams Notes
DECLARE iscn NUMBER; BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@A( source_object_name => 'schema.table', source_database_name => 'B', instantiation_scn => iscn); END; /
BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => \'schema.table\'); END; /