How can I tell if a Materialized View in Oracle is being used?

Question

We have some Materialized views in our Oracle 9i database that were created a long time ago, by a guy no longer working here. Is there an easy (or any) method to determine whether Oracle is using these views to serve queries? If they aren't being used any more, we'd like to get rid of them. But we don't want to discover after the fact that those views are the things that allow some random report to run in less than a few hours. The answer I'm dreaming of would be something like

SELECT last_used_date FROM dba_magic
WHERE materialized_view_name = 'peters_mview'

Even more awesome would be something that could tell me what actual SQL queries were using the materialized view. I realize I may have to settle for less.

If there is a solution that requires 10g, we are upgrading soon, so those answers would be useful also.

Answer 1

Oracle auditing can tell you this once configured as per the docs. Once configured, enable it by "AUDIT SELECT ON {name of materialized view}". The audit trail will be in the AUD$ table in the SYS schema.

Answer 2

One method other than auditing would be to read the v$segment_statistics view after one refresh and before the next refresh to see if there have been any reads. You'd have to account for any automatic statistics collection jobs also.

Answer 3

V$SQLAREA table has two columns which help identify the queries executed by the database.

SQL_TEXT - VARCHAR2(1000) - First thousand characters of the SQL text for the current cursor SQL_FULLTEXT - CLOB - All characters of the SQL text for the current cursor

We can use this columns to find the queries using the said materialized views