2014-08-29

DBMS_STATS.GET_PREFS for a Table

select t.owner
     , t.table_name
     , p.column_value pname
     , dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) pvalue
     , case when dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) != dbms_stats.get_prefs(p.column_value) then dbms_stats.get_prefs(p.column_value) end pvaluedefault
 from dba_tables t,
 table(sys.odcivarchar2list('AUTOSTATS_TARGET','CASCADE','DEGREE','ESTIMATE_PERCENT','METHOD_OPT','NO_INVALIDATE','GRANULARITY','PUBLISH','INCREMENTAL','STALE_PERCENT')) p
 where owner = :owner
   and table_name = :table_name
;

RAFU P AUTOSTATS_TARGET AUTO 
RAFU P CASCADE   DBMS_STATS.AUTO_CASCADE 
RAFU P DEGREE   NULL 
RAFU P ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE 
RAFU P METHOD_OPT  FOR ALL COLUMNS SIZE AUTO 
RAFU P NO_INVALIDATE  DBMS_STATS.AUTO_INVALIDATE 
RAFU P GRANULARITY  AUTO 
RAFU P PUBLISH   TRUE 
RAFU P INCREMENTAL  TRUE     FALSE
RAFU P STALE_PERCENT  10 

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.