Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

ORACLE STREAMS and ETL.

May 21,  2004
John Garmany

 

 

ORACLE STREAMS

An oracle stream, a new feature in oracle9i release 2, is an information-sharing technology, which allows the propagation and management of data, event, and transactions within oracle databases or between oracle and non-oracle databases. Oracle streams is flexible in the sense that it allows user-intervention- users can specify what information goes into the stream, the route of flow, what happens to events in the stream and how the stream terminates. It is used to capture events such as DML (insert, update and delete) and DDL (alter, drop, rename) operations.
However, very fundamental to the working of oracle stream are three elements/components namely

  • Capture

  • Staging

  • Apply

Figure 2. Architecture of oracle stream.

The capture process

The capture process is responsible for the identification of data to capture such as database changes (DDL and DML) and application generated messages. We can have either implicit capture in which the server captures DML and DDL events at a source database using oracle's default rules or explicit capture, in which a customized configuration is used to capture data using procedures.

Furthermore, the change process formats the retrieved data into events called logical change records (LCR) and they are then placed in a queue- staging environment for onward processing. Logical change records are of two types- DDL LCR and row LCR. DDL LCR refers to changes made in the database objects by issuing ALTER, RENAME, CREATE or DROP commands. Row LCR on the other hand, refers to the modification of a single row of a table by a single DML statement. This implies that updating 10 rows in a table using a single DML statement generates 10 row LCRs.

The staging process

The staging area is a queue and acts as a temporary repository for logical change records (LCR) until they are subscribed to. The subscriber (a user application or another staging area or default apply process) has control over the contents of the staging area. Therefore, the subscriber can decide which records are propagated or consumed from the queue. For events propagation from a queue to take place, a user must be the owner of the queue and appropriate privileges are needed, not only on the source queue but also on the target queue. Moreover, a particular target queue can accept events from more than one source queues

The apply process

The apply process is responsible for applying the changes to the target database. This is possible in two ways namely- Default consumption (implicit) and customized consumption (explicit). In default consumption, the apply engine is used to apply the changes to the database directly. Per adventure if a conflict occurs, the apply engine resolves it by invoking resolution (data transmission) routines.

In customized consumption, the logical change records are passed as arguments to a user-defined function for processing. If the customized procedure process DML LCRs, DDL LCRs and enqueued messages, they are called DML handlers, DDL handlers and message handlers respectively.



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational