ORA-904 While Creating Distributed MVIEW After Adding Not Null Column (Doc ID 1294800.1)

Last updated on MAY 05, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

Customer is getting following error while recreating distributed mview after adding a NOT NULL column with DEFAULT VALUE at remote db
ORA-00904: "TEST_TRB1_APPL_DEF"."SYNC_ACTV": invalid identifier

Testcase:

First Run
=============

Run at DB 1 [Base Table - remote site]:
--------------------------------------
CREATE TABLE TEST
(
ACTV_CODE_ID NUMBER(9) CONSTRAINT TRB1AD_ACTV_CODE_ID_NN NOT NULL,
SUB_APPL_ID NUMBER(9) CONSTRAINT TRB1AD_SUB_APPL_ID_NN NOT NULL,
SYS_CREATION_DATE DATE CONSTRAINT TRB1AD_SYS_CREATION_DATE_NN NOT NULL,
SYS_UPDATE_DATE DATE,
NUMERIC_FILTER NUMBER(9))
TABLESPACE USERS;


CREATE INDEX TEST_1IX ON TEST
(SUB_APPL_ID, ACTV_CODE_ID)
LOGGING
TABLESPACE USERS;


ALTER TABLE TEST ADD (
CONSTRAINT TEST_1CK
CHECK (NUMERIC_FILTER IS NULL OR NUMERIC_FILTER > 0));

desc TEST;


Run at DB 2 [Mview Site]:
-------------------------
CREATE MATERIALIZED VIEW TEST_MVIEW
TABLESPACE users
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE users
REFRESH COMPLETE ON DEMAND
WITH ROWID
AS
SELECT * FROM TEST@MURUREMOTE;

desc TEST_MVIEW

Raises error during second run:

Second Run
=============

Run at DB 1 [Base Table - remote site]:
--------------------------------------
ALTER TABLE TEST ADD( SYNC_ACTV CHAR(1 BYTE) DEFAULT 'N' CONSTRAINT TRB1AD_SYNC_ACTV_NN NOT NULL);

desc TEST;

Run at DB 2 [Mview Site]:
-------------------------
DROP MATERIALIZED VIEW TEST_MVIEW;

CREATE MATERIALIZED VIEW TEST_MVIEW
TABLESPACE users
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE users
REFRESH COMPLETE ON DEMAND WITH ROWID
AS
SELECT TEST.ACTV_CODE_ID ACTV_CODE_ID,TEST.SUB_APPL_ID SUB_APPL_ID,TEST.SYS_CREATION_DATE SYS_CREATION_DATE,
TEST.SYS_UPDATE_DATE SYS_UPDATE_DATE,TEST.NUMERIC_FILTER NUMERIC_FILTER,TEST.SYNC_ACTV SYNC_ACTV
FROM TEST@MURUREMOTE TEST;
*
ERROR at line 16:
ORA-00904: "TEST"."SYNC_ACTV": invalid identifier


Both db's are 11.1.0.7.

SELECT statement is working fine and brings newly added column.
CTAS working fine and brings newly added column
Create Mview only raises errors

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms