DBA_MVIEWS Shows STALENESS Value of UNKNOWN After Refresh (Doc ID 757537.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Symptoms

When we use a function on a view which is used in an mview, or when a function is part of the mview itself, after mview refresh staleness becomes unknown, despite compile_state remains valid

Everything works fine if no function is used, so function may cause staleness to not know real status

Mview works fine, and refresh raises no issue, but staleness still shows same status.

Changes

TEST CASE:
----------

CREATE OR REPLACE PACKAGE "TEST1_PKG" AUTHID CURRENT_USER -- invoker rights,
not definer rights
IS
FUNCTION get_val RETURN NUMBER;

END test1_pkg;
/
CREATE OR REPLACE PACKAGE BODY "TEST1_PKG" AS
FUNCTION get_val
RETURN number IS
BEGIN
RETURN 1;
END;
END;
/

CREATE TABLE TEST_1
(
C_1 NUMBER(6) NOT NULL,
S_2 VARCHAR2(6 BYTE) NOT NULL
)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
SET DEFINE OFF;

Insert into TEST_1 (C_1, S_2)
Values (1, 'a');
Insert into TEST_1 (C_1, S_2)
Values (2, 'b');
Insert into TEST_1 (C_1, S_2)
Values (3, 'c');
COMMIT;

DROP VIEW O_TEST_1;

CREATE OR REPLACE FORCE VIEW o_test_1 (n_1, n_2, n_3)
AS
SELECT c_1, s_2, test1_pkg.get_val
FROM test_1;

DROP MATERIALIZED VIEW S_TEST;

CREATE MATERIALIZED VIEW S_TEST
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT MIN (n_1), n_2
FROM o_test_1 t1
GROUP BY t1.n_2;



SELECT owner,mview_name,last_refresh_type,last_refresh_date,staleness,
compile_state,
stale_since FROM dba_mviews WHERE owner = 'TEST' AND mview_name='S_TEST';

exec dbms_mview.refresh('TEST.S_TEST');

SELECT owner,mview_name,last_refresh_type,last_refresh_date,staleness,
compile_state,
stale_since FROM dba_mviews WHERE owner = 'TEST' AND mview_name='S_TEST';


Same behavior when function is used on mview (disabling query rewrite) when
based on table:


CREATE MATERIALIZED VIEW S_TEST
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT MIN (c_1), s_2, test1_pkg.get_val
FROM TEST_1 t1
GROUP BY t1.s_2;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms