SYS_MV_REFRESH_HISTORY
The results include information about the refresh history of all materialized views. The results include the refresh type, such as manual or auto, and the status of the most recent refresh.
SYS_MV_REFRESH_HISTORY is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.
Table columns
| Column name | Data type | Description |
|---|---|---|
| user_id | integer | The identifier of the user who submitted the refresh. |
| session_id | integer | The process identifier for the process running the materialized view refresh. |
| transaction_id | bigint | The transaction identifier. |
| database_name | char(128) | The database that contains the materialized view. |
| schema_name | char(128) | The schema of the materialized view. |
| mv_id | bigint | Object ID of the materialized view. |
| mv_name | char(128) | The materialized view name. |
| refresh_type | char(32) | The type of refresh, such as manual or auto. |
| status | text | The status of the refresh. For detailed information about statuses, see the status column for SVL_MV_REFRESH_STATUS. |
| start_time | timestamp | The start time of the refresh. |
| end_time | timestamp | The end time of the refresh. |
| duration | bigint | The amount of time in microseconds it took to refresh the materialized view. |
Sample queries
The following query shows the refresh history for materialized views.
SELECT user_id, session_id, transaction_id, database_name, schema_name, mv_id, mv_name, refresh_type, status, start_time, end_time, duration from sys_mv_refresh_history;
The query returns the following sample output:
user_id | session_id | transaction_id | database_name | schema_name | mv_id | mv_name | refresh_type | status | start_time | end_time | duration ---------+------------+----------------+---------------+----------------------------+--------+--------------------+----------------+-------------------------------------------------+----------------------------+----------------------------+----------- 1 | 1073815659 | 15066 | dev | test_stl_mv_refresh_schema | 203762 | mv_incremental | Manual | MV was already updated | 2023-10-26 15:59:20.952179 | 2023-10-26 15:59:20.952866 | 687 1 | 1073815659 | 15068 | dev | test_stl_mv_refresh_schema | 203771 | mv_nonincremental | Manual | MV was already updated | 2023-10-26 15:59:21.008049 | 2023-10-26 15:59:21.008658 | 609 1 | 1073815659 | 15070 | dev | test_stl_mv_refresh_schema | 203779 | mv_refresh_error | Manual | MV was already updated | 2023-10-26 15:59:21.064252 | 2023-10-26 15:59:21.064885 | 633 1 | 1073815659 | 15074 | dev | test_stl_mv_refresh_schema | 203762 | mv_incremental | Manual | Refresh successfully updated MV incrementally | 2023-10-26 15:59:29.693329 | 2023-10-26 15:59:43.482842 | 13789513 1 | 1073815659 | 15076 | dev | test_stl_mv_refresh_schema | 203771 | mv_nonincremental | Manual | Refresh successfully recomputed MV from scratch | 2023-10-26 15:59:43.550184 | 2023-10-26 15:59:47.880833 | 4330649 1 | 1073815659 | 15078 | dev | test_stl_mv_refresh_schema | 203779 | mv_refresh_error | Manual | Refresh failed due to an internal error | 2023-10-26 15:59:47.949052 | 2023-10-26 15:59:52.494681 | 4545629 (6 rows)
