-- Querying user_snapshots for any snapshots

thiru@9.2.0:SQL>select name,table_name,query from user_snapshots;

NAME                           TABLE_NAME
------------------------------ ------------------------------
QUERY
--------------------------------------------------------------------------------
T_MVIEW                        T_MVIEW
select deptno,sum(sal) from t group by deptno

-- It shows one snapshot
-- Lets drop it. 

thiru@9.2.0:SQL>drop materialized view t_mview;

Materialized view dropped.

thiru@9.2.0:SQL>select name,table_name,query from user_snapshots;

no rows selected

-- The snapshot is gone . (ie the materiazed view is gone)

-- Lets create a materialized view

thiru@9.2.0:SQL>create materialized view MVIEW as select deptno,sum(sal) from t group by deptno;

Materialized view created.

-- and now query user_snapshots

thiru@9.2.0:SQL>select name,table_name,query from user_snapshots;

NAME                           TABLE_NAME
------------------------------ ------------------------------
QUERY
--------------------------------------------------------------------------------
MVIEW                          MVIEW
select deptno,sum(sal) from t group by deptno

-- User_snapshots shows one materialized view

-- Now,lets create a Snapshot

thiru@9.2.0:SQL>create snapshot tsnapshot as select deptno,sum(sal) from t group by deptno;

<B>Materialized view created.</B>

-- Note, it says Materialized view created when you create a snapshot.

-- Now,lets query user_mviews to show materialized views

thiru@9.2.0:SQL>select mview_name,query from user_mviews;

MVIEW_NAME                     QUERY
------------------------------ -----------------------------------------------------
MVIEW                          select deptno,sum(sal) from t group by deptno
TSNAPSHOT                      select deptno,sum(sal) from t group by deptno

-- and it shows the two snapshots (materialized views) we created earlier .