Query below lists:

(A) all materialized views, with their definition, accessible to the current user in Oracle database

(B) all materialized views, with their definition, in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. All materialized views accessible to the current user

select owner as schema_name,
       mview_name,
       container_name,
       query as definition,
       refresh_mode,
       refresh_method,
       build_mode,
       last_refresh_date,
       compile_state
from sys.all_mviews
order by owner, mview_name;

B. If you have privilege on dba_mviews

select owner as schema_name,
       mview_name,
       container_name,
       query as definition,
       refresh_mode,
       refresh_method,
       build_mode,
       last_refresh_date,
       compile_state
from sys.dba_mviews
order by owner, mview_name;

Columns

• schema_name - view owner, schema name
• mview_name - materialized view name
• container_name - name of the container with materialized view's data
• definition - materialized view script - select statement only
• refresh_mode - refresh mode of the materialized view (DEMAND/COMMIT/NEVER)
• refresh_method - default method userd to refresh materialized view (COMPLETE/FORCE/FAST/NEVER)
• build_mode - how the materialized view was populated during creation (IMMEDIATE/DEFERRED/PREBUILT)
• last_refresh_date - date of the last refresh of the materialized view
• compile_state - indicates validity of the materialized view (VALID/NEEDS_COMPILE/ERROR)

Rows

• One row represents one materialized view in a database
• Scope of rows: (A) all materialized views, with their definition, accessible to the current user in Oracle database, (B) all materialized views, with their definition, in Oracle database
• Ordered by schema name, materialized view name

Sample results

Here is a view of database views in Oracle SQL Developer: