RE: Drawing a blank and can't find a quick answer - view for incremental stats enabled tables?

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-l