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.
