Re: Mat View - used by?
- From: Anthony Ballo <anthony.ballo@xxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Dec 2011 10:16:56 -0800
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@xxxxxxxxx<mailto:tim@xxxxxxxxx>>
Reply-To: "tim@xxxxxxxxx<mailto:tim@xxxxxxxxx>"
<tim@xxxxxxxxx<mailto:tim@xxxxxxxxx>>
Date: Mon, 12 Dec 2011 09:52:38 -0800
To: Anthony Ballo
<anthony.ballo@xxxxxxxxxxx<mailto:anthony.ballo@xxxxxxxxxxx>>,
"'oracle-l@xxxxxxxxxxxxx<mailto:'oracle-l@xxxxxxxxxxxxx>'"
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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@xxxxxxxxxxx]
Sent: Monday, December 12, 2011 10:47 AM
To: 'oracle-l@xxxxxxxxxxxxx<mailto:'oracle-l@xxxxxxxxxxxxx>'
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 --
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
