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> on behalf of Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> Reply-To: <jonathan_at_jlcomp.demon.co.uk> Date: Wednesday, 21 March 2018 at 15:35
To: <christopherdtaylor1994_at_gmail.com> Cc: ORACLE-L <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> 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