Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Materialized view

560551Jul 31 2009 — edited Jul 31 2009
Hello 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 28 2009
Added on Jul 31 2009
2 comments
384 views