Limitations of Oracle Streams?

 

Question:  Are you aware of any limits on the number of tables that can be replicated using material views or Oracle Streams?

We are involved in a project where we need to replicate data for over 1,200 tables and needed to know if we?re going to run into any limits as to the number of tables that can be replicated that would eliminate either of the methods from consideration.

Answer:  I'm not aware of ANY limit other than Oracle's limit of about 10 zillions tables.  I've never done 1k tables, but we have done 500 with no problem.  There will be limitations based on DML traffic volume, and you need to do a benchmark to determine the breaking point for your database.  A performance benchmark is a required best practice for all replication environments, as due diligence. 

Here is a sample benchmark for Streams replication.

Remember that Materialized Views are replications, and Oracle stores the changed data in a snapshot log.  The only other overhead is the refresh process which ships the changes to the materialization.  The only limits will be those from your configuration parameters.

Streams uses Advanced Queuing and replicates using the log buffer, so I don't see any limitations if properly configured.

The book "Oracle Streams" is a good source for the configuration and setup information.