Database A 10G Enterprise 64bit on Windows 2008. ( Source: Transactional - low frequency updates e.g. less than 100 trx per hour).
Database B 10G Standard 64bit on Windows 2008. ( Target: BI analytical / adhoc reporting - some data aggregation maybe expected ).
I have a 5G table (w/ PK) on database A and would like to synchronize (online or even near-realtime ) this table to Database B one-way direction only. What is the efficient method to do so...? CDC? Replication or...?
This is very helpful.
I wonder if I should create all columns on the MV Log in the source or have this setting as default?
And just to confirm that FAST refresh does not need the UPDATE switch at MV creation, is this correct ( as only single direction update is needed ) ?
Lastly, I presume if did an impdp / imp into Database B from Database A ( only the table in discussion ), i just need to redo the same procedure once ?
And, no, you don't need UPDATE on creation. It sounds like you only need "one-way direction" so the default READ ONLY should be sufficient for your needs. You lost me on the impdp/imp question. There is no need to exp/imp, the CREATE MATERIALIZED VIEW is going to do all of the initial heavy lifting. Depending on how you create it you can schedule the fast refresh interval with NEXT (every five minutes for example):
CREATE MATERIALIZED VIEW catalog
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 5/24/60
WITH PRIMARY KEY
AS SELECT * FROM product_information@orcl1;