CODEWITHSUNDEEP
oracle-database
Software Sainath
Software Sainath

Reputation: 1050

How to get list of all materialized views in oracle

How to get list of all Materialized Views.?

Upvotes: 27

Views: 120227

Answers (4)

miracle173
miracle173

Reputation: 1973

Actually ALL_MVIEWS and ALL_SNAPHOTS displays only the views the user has granted access on. To see all views in a database you must query DBA_MVIEWS or DBA_SNAPHOTS. You need special privileges or roles to query this view like the system privilege SELECT ANY DICTIONARY or the role SELECT_CATALOG_ROLE. A similar statement holds for other ALL_ and DBA_ views.

Upvotes: 3

Hongtao
Hongtao

Reputation: 197

I never use all_snapshots before.

Here is another way to do:

select * from all_objects where OBJECT_TYPE='MATERIALIZED VIEW';

Upvotes: 3

diederikh
diederikh

Reputation: 25281

Try this:

SELECT *
FROM  all_snapshots;

Instead of all_snapshots you can also use the all_mviews view.

Upvotes: 36

Petr Pribyl
Petr Pribyl

Reputation: 3575

select * from all_mviews;

or

select * from dba_mviews;

Upvotes: 23

Related Questions