Environment: DB is Oracle 11g
I wanted to replicate part of my database in a offline laptop, using Materialized views (and refreshing them when the laptop would be online with the master DB).
So far, so good.
Step 2 was to allow some of those views to be changeable. So, I decided to use updatable MVs.
Question1: to create an updatable view, the master table must have a primary key?
Question2: the updatable command overrides the fast refresh, or they can (should) be used together?
I created the MV Log in the master
then the refresh group in remote
DBMS_REFRESH.MAKE ( name => 'my_group',
list => '',
next_date => '' , interval => '',
implicit_destroy => FALSE, rollback_seg => '',
push_deferred_rpc => TRUE, refresh_after_errors => FALSE);
CREATE MATERIALIZED VIEW "TARGET_ACTIVITY" ("ID", "FAO_CODE", "NAME", "PARENT_ID") BUILD IMMEDIATE
REFRESH FORCE ON DEMAND NEXT NULL
USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS FOR UPDATE DISABLE QUERY REWRITE AS
SELECT "TARGET_ACTIVITY"."ID" "ID",
FROM "TARGET_ACTIVITY"@"SERVIDOR" "TARGET_ACTIVITY";
Added view to refresh group
DBMS_REFRESH.add( name => 'my_group', list => 'TARGET_ACTIVITY', lax => TRUE);
But whenever I refresh the group, the updates in the remote table are deleted and they do not update the master... shouldn't they pass to the master table AND stay in the remote table? Or did I get this updatable thing all wrong?
Thanks for any help
Did you think about using import-export utility for this like imp/exp or impdp/expdp (could be done thought network - NETWORK_LINK)
Or you could use Database Advanced Replication http://docs.oracle.com/cd/B19306_01/server.102/b14226/toc.htm
for example Updatable Materialized Views http://docs.oracle.com/cd/B19306_01/server.102/b14226/repmview.htm#sthref396
As I read, my mistake is not putting the MV in a MV group. I have in a REFRESH_GROUP, which is a different concept?(???) Updatable materialized views have the following properties. They are always based on a single table, although multiple tables can be referenced in a subquery. They can be incrementally (or fast) refreshed. Oracle propagates the changes made to an updatable materialized view to the materialized view's remote master table or master materialized view. The updates to the master then cascade to all other replication sites.
I should create a MV group, and add it to the refresh group??
PS: I just realized there is a REPLICATION forum... is there a way to pass this thread there, or do I need to create a new one?