Hi,

I am trying to create a materialized view which has subset of columns of decision_SCC at remote server.
I create a table (mv_decision_scc) locally that has 3 columns of the original table (decision_scc) at remote server
But once I tried to use ON PREBUILT TABLE command to create materialized view (mv_decision_scc) locally, I got error, please tell me what I am missing?

Thanks,
Fiona

--============================================================================--
-- At remote server site under data_fix schema
--============================================================================--
CREATE TABLE data_fix.decision_SCC
(
SCAC VARCHAR2(4) NOT NULL,
SCC VARCHAR2(45) NOT NULL,
VERIFICATION_DATE DATE,
AGAINST_MV_COUNT NUMBER,
APPROVER_NAME VARCHAR2(32),
FILE_SENT_DATE DATE NOT NULL,
CREATE_USER VARCHAR2(32) NOT NULL,
CREATE_DATE DATE NOT NULL,
UPDATE_USER VARCHAR2(32) NOT NULL,
UPDATE_DATE DATE NOT NULL
)
TABLESPACE data_fix_data
CACHE
NOPARALLEL
LOGGING
/
--------------------------------------------------------------------------------
-- create primary key
ALTER TABLE data_fix.decision_SCC
ADD CONSTRAINT pk_scac_scc PRIMARY KEY (SCAC, SCC, FILE_SENT_DATE)
/
--------------------------------------------------------------------------------
-- CREATE a MV log so the MV can be refreshed in dwdev
CREATE MATERIALIZED VIEW LOG ON data_fix.decision_SCC
TABLESPACE data_fix_data
WITH PRIMARY KEY
INCLUDING NEW VALUES
/

select * from data_fix.decision_SCC;

SCAC SCC VERIFICATION_DATE AGAINST_MV_COUNT APPROVER_NAME FILE_SENT_DATE CREATE_USER CREATE_DATE UPDATE_USER UPDATE_DATE
---- --------- ---------------------- ---------------- ------------- ---------------------- ----------- -------------------- ----------- --------------------
HLCU 1398890 42 10/15/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM
CHNJ 13TP01706 10/23/2015 12:00:00 AM 382 James Sanders 10/15/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM
HLCU 1433068 25 11/16/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM
HLCU 1445649 43 10/15/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM
HLCU 1457130 94 11/16/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM
HLCU 1493671 32 10/15/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM
CHNJ 14TL09265 10/23/2015 12:00:00 AM 57 James Sanders 10/15/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM
CHNJ 14TP01720 10/23/2015 12:00:00 AM 79 James Sanders 10/15/2015 12:00:00 AM BIN202 12/8/2015 5:13:00 PM BIN202 12/8/2015 5:13:00 PM


--============================================================================--
-- At local server site under ps_system schema
--============================================================================--
-- create a table with the same structure as in remote server with sub-set of columns
-- in order to be used for Materialized view rebuild
CREATE TABLE ps_system.mv_decision_scc AS
SELECT scac, scc, approver_name, file_sent_date FROM data_fix.decision_scc@remote_svr
/

--------------------------------------------------------------------------------
ALTER TABLE ps_system.mv_decisioned_SCC
ADD CONSTRAINT pk_scac_scc PRIMARY KEY (SCAC, SCC, FILE_SENT_DATE)
/

--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW ps_system.mv_decisioned_scc
REFRESH FORCE
ON DEMAND WITH PRIMARY KEY
ON PREBUILT TABLE WITH REDUCED PRECISION
ENABLE query rewrite
AS
SELECT scac, scc, approver_name, file_sent_date FROM data_fix.decisioned_scc@remote_svr WHERE scac = 'CMDU'
/

==> GOT ERROR [1]: ORA-00905: missing keyword

CREATE MATERIALIZED VIEW mv_decisioned_scc
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FORCE
ON DEMAND 
ENABLE query rewrite