What’s preference_name in the below query?
BA
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Vishal Gupta
Sent: Thursday, March 22, 2018 1:52 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; christopherdtaylor1994@xxxxxxxxx
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on
behalf of Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>
Reply-To: <jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>
Date: Wednesday, 21 March 2018 at 15:35
To: <christopherdtaylor1994@xxxxxxxxx<mailto:christopherdtaylor1994@xxxxxxxxx>>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx<mailto: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<mailto: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
