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@xxxxxxxxxxxxx> on behalf of Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx>
Reply-To: <jonathan@xxxxxxxxxxxxxxxxxx>
Date: Wednesday, 21 March 2018 at 15:35
To: <christopherdtaylor1994@xxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx> 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
