RE: Drawing a blank and can't find a quick answer - view for incremental stats enabled tables?
Date: Thu, 22 Mar 2018 21:53:37 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423E031FF6DBF9_at_plt-exch-01.Itradenetwork.com>
What’s preference_name in the below query?
BA
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vishal Gupta
Sent: Thursday, March 22, 2018 1:52 PM
To: jonathan_at_jlcomp.demon.co.uk; christopherdtaylor1994_at_gmail.com
Cc: ORACLE-L
Subject: Re: Drawing a blank and can't find a quick answer - view for incremental stats enabled tables?
You can use the query like below to get table level stats preferences.
- This query has been taken from DBA_TAB_STAT_PREFS sys view.
- chgtime column is not exposed in the view, so querying from underlying
- objects directly
SELECT u.username owner
, o.object_name table_name
, p.pname preference_name
, p.valchar preference_value
, p.chgtime ChangeTime
FROM sys.optstat_user_prefs$ p , dba_objects o , dba_users u WHERE p.obj#=o.object_id AND u.username=o.owner AND u.username like UPPER('&&owner') ESCAPE '\' AND o.object_name like UPPER('&&table_name') ESCAPE '\' AND UPPER(p.pname) like UPPER('&&preference_name') ESCAPE '\' ORDER BY u.username , o.object_name , p.pname ;
Regards,
Vishal Gupta
From: <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>
Reply-To: <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>
Date: Wednesday, 21 March 2018 at 15:35
To: <christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>>
Cc: ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>
Subject: Re: Drawing a blank and can't find a quick answer - view for incremental stats enabled tables?
Check view dba_tab_stat_prefs.
There’s no equivalent user view
Regards
Jonathan Lewis
(From my iPad mini; please excuse typos and auto-correct)
On 21 Mar 2018, at 14:58, Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>> wrote: Is there a system view that shows which tables INCREMENTAL stats have been setup on?
I know I can do:
DBMS_STATS.get_prefs and pass in the table table and owner but surely there's a view that I can query?
But I can't find it - we're on 12.1.0.2.
Thanks,
Chris
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 22 2018 - 22:53:37 CET