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

Preference can be any of the following.  

DBMS_STATS.SET_TABLE_PREFS documentation -  https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68674  

CASCADE
DEGREE
ESTIMATE_PERCENT
GRANULARITY
INCREMENTAL
INCREMENTAL_LEVEL
INCREMENTAL_STALENESS
METHOD_OPT
NO_INVALIDATE
PUBLISH
STALE_PERCENT
TABLE_CACHED_BLOCKS
OPTIONS     Regards,

Vishal Gupta

From: Bheemsen Aitha <baitha_at_itradenetwork.com> Date: Thursday, 22 March 2018 at 21:53
To: "vishal_at_vishalgupta.com" <vishal_at_vishalgupta.com>, "jonathan_at_jlcomp.demon.co.uk" <jonathan_at_jlcomp.demon.co.uk>, "christopherdtaylor1994_at_gmail.com" <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?  

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