Re: Mat View - used by?

  • From: "Tim Gorman" <tim@xxxxxxxxx>
  • To: anthony.ballo@xxxxxxxxxxx, "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 12 Dec 2011 17:52:38 +0000

Look for the name of the MV in the OBJECT_NAME column of DBA_HIST_SQL_PLAN, 
which will give you a list of SQL_IDs, not people or users. However, you can 
then query DBA_HIST_ACTIVE_SESS_HISTORY for session information (including 
USER_ID, MODULE, ACTION, etc) based on those SQL_IDs.
Just be aware that the AWR information is not an audit trail, but sampled data, 
so it is not perfect or definitive.

But it is way, WAY, *WAY* better than nothing... :-)

Happy hunting!

-----Original Message-----
From: Anthony Ballo [mailto:anthony.ballo@xxxxxxxxxxx]
Sent: Monday, December 12, 2011 10:47 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Mat View - used by?

Hello,I'm trying to determine if we can lengthen the time to refresh on 
aparticular Mat View in Is there a way to see (via SQL?) whatother 
code utilizes this Materialized View (like a "used by")? It iscurrently on a 
1/48 (every half hour) refresh which seems excessive to me. The naming of the 
view would suggest that it is used in 


Other related posts: