Materialized view
560551Jul 31 2009 — edited Jul 31 2009Hello all
oracle 9i
linux rhel 4 43 bits
I have a question about materialized views...Every morning I see with this query:
SELECT object_name FROM USER_OBJECTS WHERE status='INVALID'
a few materialized view INVALID...So I recompile them with:
ALTER MATERIALIZED VIEW VRHU_MAT COMPILE
It works...but later all the MAT view are INVALID AGAIN..I don't know why, I do not see error in alertlog.
I think it could be a refresh issue, but I'm not sure.
Here is the code of one of them:
CREATE TABLE VRHU_MAT_NOM
(
ICI les camps avec les types
)
TABLESPACE TBS
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
NOMONITORING;
COMMENT ON TABLE LATABLE IS 'snapshot table for snapshot';
CREATE INDEX IDX_MAT_PERFILOPCION_IDPERFIL ON TABLE
(IDL)
LOGGING
TABLESPACE TBS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX IDX_xx ON TABLE
(ID)
LOGGING
TABLESPACE TBS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Any suggestions?
THanks a lot