Re: Mat View - used by?

From: Anthony Ballo <anthony.ballo_at_onecall.com>
Date: Mon, 12 Dec 2011 10:16:56 -0800
Message-ID: <CB0B84CF.15363%anthony.ballo_at_onecall.com>



Thanks Tim!
WITH The column: MODULE, I was able to provide a lead to track this one down to one user:

        SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SQL_ID IN (         select SQL_ID from DBA_HIST_SQL_PLAN

        where OBJECT_NAME = 'XX_MY_MV')

From: Tim Gorman <tim_at_evdbt.com<mailto:tim_at_evdbt.com>> Reply-To: "tim_at_evdbt.com<mailto:tim_at_evdbt.com>" <tim_at_evdbt.com<mailto:tim_at_evdbt.com>> Date: Mon, 12 Dec 2011 09:52:38 -0800
To: Anthony Ballo <anthony.ballo_at_onecall.com<mailto:anthony.ballo_at_onecall.com>>, "'oracle-l_at_freelists.org<mailto:'oracle-l_at_freelists.org>'" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: Mat View - used by?

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_at_onecall.com] Sent: Monday, December 12, 2011 10:47 AM To: 'oracle-l_at_freelists.org<mailto:'oracle-l_at_freelists.org>' Subject: Mat View - used by?

Hello, I'm trying to determine if we can lengthen the time to refresh on a particular Mat View in 10.2.0.4. Is there a way to see (via SQL?) what other code utilizes this Materialized View (like a "used by")? It is currently 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 Discoverer reporting. Thanks, Anthony -- http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 12 2011 - 12:16:56 CST

Original text of this message