Re: struggeling with replication

Argh! Thought I had this down, now I just need another set of eyes! I hate to keep coming back to the well on this, but don't know what else to do..

I have a total of 10 MV's to create. Extracted the DDL from the original database, saved them in 10 .sql files, named mv_<mvname>.sql. Prepended a 'drop materialized view' statement in each. Created a master script, build_mv.sql, that looks like this:

spool build_mv.log
@mv_name1.sql
@mv_name2.sql

-etc.

First run, everything looked great. Ran it again and now CREATE MATERIALIZED VIEW statement for 3 of the mv's returned something like:

SQL> CREATE MATERIALIZED VIEW "NOBELEPS"."REP_LA_TXLOG" PCTFREE 10

  2  	 PCTUSED 40 MAXTRANS 255
  3  	 STORAGE ( INITIAL 5120K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
  4  	 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
  5  	 TABLESPACE "DATA"  BUILD IMMEDIATE
  6  	 USING INDEX
  7  	 TABLESPACE "INDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
  8  	 STORAGE ( INITIAL 10240K NEXT 10240K PCTINCREASE 0 MINEXTENTS
  9  	 1 MAXEXTENTS 4096 FREELISTS 1 FREELIST GROUPS 1 )  REFRESH
 10  	 FAST  START WITH to_date('26-Apr-2006 09:12:34 AM',
 11  	 'dd-Mon-yyyy HH:MI:SS AM')  NEXT sysdate + 15/(60*24)	AS
 12  	 SELECT "LA_TXLOG"."TXN_ID" "TXN_ID","LA_TXLOG"."TM_TS"
 13  	 "TM_TS","LA_TXLOG"."STATUS" "STATUS",
 14  	 "LA_TXLOG"."OH_RESP_CODE" "OH_RESP_CODE","LA_TXLOG"."RRNO"
 15  	 "RRNO","LA_TXLOG"."AUTH_DATETIME" "AUTH_DATETIME",
 16  	 "LA_TXLOG"."AMOUNT" "AMOUNT","LA_TXLOG"."TOTALAUTHAMOUNT"
 17  	 "TOTALAUTHAMOUNT","LA_TXLOG"."APPRCODE" "APPRCODE",
 18  	 "LA_TXLOG"."SWITCHING_MODE" "SWITCHING_MODE",
 19  	 "LA_TXLOG"."DESTINED_HOSTNAME" "DESTINED_HOSTNAME",
 20  	 "LA_TXLOG"."PROCESSING_HOSTNAME" "PROCESSING_HOSTNAME",
 21  	 "LA_TXLOG"."OH_TXNTYPE" "OH_TXNTYPE",
 22  	 "LA_TXLOG"."ORIGINAL_TXNID" "ORIGINAL_TXNID"
 23  	 FROM "LA_TXLOG"@EPS_LINK.US.ORACLE.COM "LA_TXLOG"

    "LA_TXLOG"."ORIGINAL_TXNID" "ORIGINAL_TXNID"     *
ERROR at line 22:
ORA-00904: "LA_TXLOG"."ORIGINAL_TXNID": invalid identifier

(in all of the individual scripts, I pasted in the CREATE statement exactly as copied from the OEM window presented when doing a 'show object ddl..' .. thus the 'not pretty' line breaks) Received on Wed Apr 26 2006 - 09:40:51 CDT