You can request information, such as the status of a materialized view, using the sa_materialized_view_info system procedure. See sa_materialized_view_info system procedure, and Materialized view statuses and properties.

When a materialized view is created, the options in force when the index was created is stored with the view for future use. To retrieve the option settings used during creation of a materialized view, execute the following statement:

SELECT b.object_id, b.table_name, a.option_id, c.option_name, a.option_value
FROM SYSMVOPTION a, SYSTAB b, SYSMVOPTIONNAME c
WHERE a.view_object_id=b.object_id
AND b.table_type=2;

A table_type of 2 in the SYSTAB view is a materialized view.

To determine the list of views dependent on a materialized view, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.

Dependency information can also be found in the SYSDEPENDENCY system view. See SYSDEPENDENCY system view.

 See also