Oracle Database Discussions

HI,

I created a materialized view that is based on a synonym, depending on the table you want to modify using the synonym to point to another table, but to cool it still uses the original referenced table synonym.

Is there a way, without deleting the materialized view, that the refresh will change the definition?

Example Post

CREATE OR REPLACE SYNONYM DWH.SYN_CCBCON4 FOR DWHADC.CCBCON4D;

CREATE MATERIALIZED VIEW DWH.VWM_CTO_INFO_0 (CONTRATO,FCH_ENTRADA)
TABLESPACE TS_DWH_01
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT contrato contrato, MIN (fch_entrada) fch_entrada
FROM syn_ccbcon4
WHERE NVL (fecha_modificacion, TO_DATE (fch_entrada, 'YYYYMMDD')) >=
f_calc_max_fch_modif ('CTO_INFO', 'FCH_MODIF', '0')
AND posic_actual NOT IN ('030', '050')
GROUP BY contrato;

--> REFRESH MV

Change SYNONYM

CREATE OR REPLACE SYNONYM DWH.SYN_CCBCON4 FOR DWHADC.CCBCON5D;

--> REFRESH MV

But the data is of the table CCBCON4D.

thankx

Jorge.