My Oracle Support Banner

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

Last updated on AUGUST 19, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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

To view full details, 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 a vibrant support community of peers and Oracle experts.