Database Software

Hi there.

I am attempting to implement replication of several small tables between two databases on two physical servers.

Both are running 10.2.0.4 64-bit for Windows.

The servers are separated by a database link.

The credentials in the database link correspond to an ID which only has SELECT permissions on the target tables and their MV Logs.

My test process for confirming the behaviour of the authentication, etc., was as follows (pardon the occasional pseudoSQL):

SOURCE SERVER:
-create table SRC_SCHEMA.REPL_TEST (repl_col1 INTEGER);
-insert into REPL_TEST values (1);
-insert into REPL_TEST values (2);
-insert into REPL_TEST values (3);

TARGET SERVER:
-select * from SRC_SCHEMA.REPL_TEST@DBLINK.WORLD;
--- (got results)

SOURCE SERVER:
-create materialized view log on SRC_SCHEMA.REPL_TEST;
--- OK

-grant select on REPL_TEST to TGT_SCHEMA;
-grant select on MVLOG$_REPL_TEST to TGT_SCHEMA
--- OK

TARGET SERVER:
-create materialized view TGT_SCHEMA.REPL_TEST as SELECT * FROM SRC_SCHEMA.REPL_TEST@DBLINK.WORLD;
--- MATERIALIZED VIEW CREATED

-exec dbms_mview.refresh('REPL_TEST'), 'C');
--- success

-exec dbms_mview.refresh('REPL_TEST'), 'F');
--- success

**** so that all basically works.

The tables that I actually want to replicate are created by an application that I know little about (and which I am not managing).

My results when trying to replicate these are:

--- TABLE ALREADY EXISTS

SOURCE SERVER
-create materialized view log on SRC_SCHEMA.REPL_PROD;
--- OK

-grant select on REPL_PROD to TGT_SCHEMA;
-grant select on MVLOG$_REPL_PROD to TGT_SCHEMA
--- OK

TARGET SERVER:
-create materialized view TGT_SCHEMA.REPL_PROD as SELECT * FROM SRC_SCHEMA.REPL_PROD@DBLINK.WORLD;

ERROR at line 1:
-ORA-21700: object does not exist or is marked for delete

**** so here, the door's slammed on me right away.

I tried altering my CREATE MV statement to only select the PK value, in case there were some weird datatypes at work. But I have nothing.

I am trying to find out more about the 'marked for delete' state, to understand how it is changed, how it can be 'un-marked'...

And I'm not finding much to this point.

I'd appreciate any assistance...

Thanks:

Doug W.