Staleness not Changed for Mviews with SYS Objects (Doc ID 949139.1)

Last updated on FEBRUARY 22, 2024

Applies to:

Symptoms

The STALENESS column in DBA_MVIEWS is not changing despite DML on the base tables.
There is no error reported during mview creation nor at refresh.
This behavior is just seen when the mview is based on SYS objects.

If there are no SYS object, mview behaves as expected.

Changes

Testcase:

SQL> create table T1 (col1 int, col2 varchar(20));
Table created.

SQL> create index T1I on T1 (col1);
Index created.

SQL> drop materialized view T1_V;
Materialized view dropped.

SQL> create materialized view T1_V
2 nocache nologging nocompress no parallel build deferred
3 refresh complete on demand with primary key
4 as
5 select col1 from T1
6 union
7 select 1 from dual;
Materialized view created.

SQL> exec dbms_mview.refresh ('T1_V');
PL/SQL procedure successfully completed.

SQL> select MVIEW_NAME,UPDATABLE, UPDATE_LOG, REFRESH_MODE,                            REFRESH_METHOD,LAST_REFRESH_TYPE, LAST_REFRESH_DATE,STALENESS
from all_mviews
where mview_name = 'T1_V';

MVIEW_NAME                  U      UPDATE_LOG    REFRESH_MODE    REFRESH_METHOD
------------------------------ - ----------------------- --------------------- -------------------------
LAST_REFRESH_TYPE    LAST_REFRESH_DATE      STALENESS
--------------------------- -------------------------------- -------------------
T1_V                                     N                                       DEMAND              COMPLETE
COMPLETE                            19-NOV-08                          FRESH

SQL> insert into T1 values (4,'tutu');
1 row created.

SQL> commit;
Commit complete.

SQL> select MVIEW_NAME,UPDATABLE, UPDATE_LOG, REFRESH_MODE,                            REFRESH_METHOD,LAST_REFRESH_TYPE, LAST_REFRESH_DATE,STALENESS
from all_mviews
where mview_name = 'T1_V';

MVIEW_NAME                   U UPDATE_LOG            REFRESH_MODE REFRESH_METHOD
------------------------------ - ----------------------- --------------------- -------------------------
LAST_REFRESH_TYPE   LAST_REFRESH_DATE          STALENESS
--------------------------- -------------------------------- -------------------
T1_V                                    N                                       DEMAND                 COMPLETE
COMPLETE                       19-NOV-08                                FRESH

SQL> drop materialized view T1_V;
Materialized view dropped.

SQL> create materialized view T1_V
2 nocache nologging nocompress noparallel build deferred
3 refresh complete on demand with primary key
4 as
5 select col1 from T1
6 union
7 select col1 from T1;
Materialized view created.

SQL> exec dbms_mview.refresh ('T1_V');
PL/SQL procedure successfully completed.

SQL> select MVIEW_NAME,UPDATABLE, UPDATE_LOG, REFRESH_MODE,                            REFRESH_METHOD,LAST_REFRESH_TYPE, LAST_REFRESH_DATE,STALENESS
from all_mviews
where mview_name = 'T1_V';

MVIEW_NAME                   U UPDATE_LOG           REFRESH_MODE REFRESH_METHOD
------------------------------ - ----------------------- --------------------- -------------------------
LAST_REFRESH_TYPE     LAST_REFRESH_DATE        STALENESS
--------------------------- -------------------------------- -------------------
T1_V                                    N                                        DEMAND                COMPLETE
COMPLETE                           19-NOV-08                           FRESH

SQL> insert into T1 values (4,'tutu');
1 row created.

SQL> commit;
Commit complete.

SQL> select MVIEW_NAME,UPDATABLE, UPDATE_LOG, REFRESH_MODE,                            REFRESH_METHOD,LAST_REFRESH_TYPE, LAST_REFRESH_DATE,STALENESS
from all_mviews
where mview_name = 'T1_V';

MVIEW_NAME                   U UPDATE_LOG         REFRESH_MODE    REFRESH_METHOD
------------------------------ - ----------------------- --------------------- -------------------------
LAST_REFRESH_TYPE    LAST_REFRESH_DATE         STALENESS
--------------------------- -------------------------------- -------------------
T1_V                                     N                                        DEMAND                  COMPLETE
COMPLETE                        19-NOV-08                          NEEDS_COMPILE

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.