DBA_MVREF_STMT_STATS

Last Updated:2026-09-03 07:31:12

Note

This view is available starting with V4.3.0.

Purpose

The DBA_MVREF_STMT_STATS view describes information related to refresh statements.

Columns

Column	Type	Nullable	Description
MV_OWNER	VARCHAR2(128)	YES	The name of the materialized view owner.
MV_NAME	VARCHAR2(128)	YES	The name of the materialized view.
REFRESH_ID	NUMBER	NO	The refresh ID.
RETRY_ID	NUMBER	NO	The ID of the batch to which the refresh step belongs. Note This column is available starting with V4.4.2 BP2.
STEP	NUMBER	NO	The step number, which starts from 1.
SQLID	VARCHAR2(32)	YES	The SQL ID.
STMT	CLOB	NO	The SQL text.
START_TIME	TIMESTAMP(6)	YES	The start time of the SQL statement or action corresponding to the refresh step. Note This column is available starting with V4.4.2 BP2.
EXECUTION_TIME	NUMBER	NO	The SQL execution time. In versions earlier than V4.4.2 BP2, the unit is seconds. In V4.4.2 and later, the unit is microseconds.
EXECUTION_PLAN	CLOB	YES	For internal troubleshooting only. Note This column is compatible only, and its value is NULL by default.
RESULT	NUMBER	NO	The execution result code of the refresh step. Valid values: 0: successful execution. 1: pending execution or in progress. Note This column is available starting with V4.4.2 BP2.

Sample query

Query information related to refresh statements.

obclient [SYS]> SELECT * FROM SYS.DBA_MVREF_STMT_STATS;

The query result is as follows:

+----------+---------+------------+------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
| MV_OWNER | MV_NAME | REFRESH_ID | STEP | SQLID                            | STMT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | EXECUTION_TIME | EXECUTION_PLAN |
+----------+---------+------------+------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
| SYS      | T1_MV1  |    1409121 |    1 | DA98E3A3F4383EC975C94A378A5679A5 | merge into "SYS"."T1_MV1" using (select "DLT_T$$"."C2" AS "C2",sum("DLT_T$$"."DMLFACTOR$$") AS "CNT",sum(("DLT_T$$"."DMLFACTOR$$" * (case when ("DLT_T$$"."C3" is null) then 0 else 1 end))) AS "CNT_C3",sum(("DLT_T$$"."DMLFACTOR$$" * "DLT_T$$"."C3")) AS "SUM_C3" from (select "MLOG$_T1"."OLD_NEW$$" AS "OLD_NEW$$","MLOG$_T1"."SEQUENCE$$" AS "SEQUENCE$$",(case when ("MLOG$_T1"."OLD_NEW$$" = 'N') then 1 else -1 end) AS "DMLFACTOR$$",max("MLOG$_T1"."SEQUENCE$$") over( PARTITION BY "MLOG$_T1"."C1") AS "MAXSEQ$$",min("MLOG$_T1"."SEQUENCE$$") over( PARTITION BY "MLOG$_T1"."C1") AS "MINSEQ$$","MLOG$_T1"."C2" AS "C2","MLOG$_T1"."C3" AS "C3" from "SYS"."MLOG$_T1" where ("MLOG$_T1"."ORA_ROWSCN" > 1706148205539021229) and ("MLOG$_T1"."ORA_ROWSCN" <= 1706148239301769113)) "DLT_T$$" where ((("DLT_T$$"."OLD_NEW$$" = 'N') and ("DLT_T$$"."SEQUENCE$$" = "DLT_T$$"."MAXSEQ$$")) or (("DLT_T$$"."OLD_NEW$$" = 'O') and ("DLT_T$$"."SEQUENCE$$" = "DLT_T$$"."MINSEQ$$"))) group by "DLT_T$$"."C2") "DLT_BASIC_MV$$" on ( ("T1_MV1"."C2" <=> "DLT_BASIC_MV$$"."C2") ) when matched then update set "T1_MV1"."CNT" = ("T1_MV1"."CNT" + "DLT_BASIC_MV$$"."CNT"), "T1_MV1"."CNT_C3" = ("T1_MV1"."CNT_C3" + "DLT_BASIC_MV$$"."CNT_C3"), "T1_MV1"."SUM_C3" = (case when (("T1_MV1"."CNT_C3" + "DLT_BASIC_MV$$"."CNT_C3") = 0) then NULL when ("T1_MV1"."CNT_C3" = 0) then "DLT_BASIC_MV$$"."SUM_C3" when ("DLT_BASIC_MV$$"."CNT_C3" = 0) then "T1_MV1"."SUM_C3" else ("T1_MV1"."SUM_C3" + "DLT_BASIC_MV$$"."SUM_C3") end) delete where ("T1_MV1"."CNT" = 0) when not matched then insert ( C2 , CNT , CNT_C3 , SUM_C3 ) values ( "DLT_BASIC_MV$$"."C2" , "DLT_BASIC_MV$$"."CNT" , "DLT_BASIC_MV$$"."CNT_C3" , (case when ("DLT_BASIC_MV$$"."CNT_C3" = 0) then NULL else "DLT_BASIC_MV$$"."SUM_C3" end) ) where ("DLT_BASIC_MV$$"."CNT" <> 0) |              0 | NULL           |
+----------+---------+------------+------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+

Previous topic

DBA_MVREF_STATS_SYS_DEFAULTS

Last

Next topic

DBA_OB_ACCESS_POINT