Top 60 Oracle Blogs

Recent comments

Printing all table preferences affecting dbms_stats.gather_table_stats

Oracle 11g introduced the abilty to control the behaviour of the dbms_stats package by setting preferences on the database, schema, and table level. These affect the way dbms_stats goes about doing its work. This feature has been extensively documented, I found the post by Maria Colgan exceptionally good at explaining the mechanism.

I often have to check table preferences in case statistics look a little suspicious but there isn’t an Oracle supplied way that I’m aware of to print all table preferences so I came up with a little script to do so. It currently supports Oracle 19c but it’s super easy to update the code for other releases. The table – passed as a parameter – needs to be in the user’s schema:

$ cat table_prefs.sql 
set serveroutput on verify off

prompt getting table prefs for &1
prompt ----------------------------------------

        v_version varchar2(100);
        v_compat  varchar2(100);

        type prefs_t is table of varchar2(100);

        v_prefs_19c prefs_t := prefs_t(

        procedure print_prefs(pi_prefs prefs_t) as
                v_value varchar2(100);
                for i in pi_prefs.first .. pi_prefs.last loop
                        v_value := sys.dbms_stats.get_prefs(
                                pname => pi_prefs(i),
                                ownname => user,
                                tabname => sys.dbms_assert.sql_object_name('&1'));

                sys.dbms_output.put_line(rpad(pi_prefs(i), 50) || ': ' || v_value);
                end loop;

        sys.dbms_utility.db_version(v_version, v_compat);

        if v_version = '' then
                raise_application_error(-20001, 'Oracle ' || v_version || ' not yet supported');
        end if;


Extending the code is very straight-forward. Simply create a new variable like v_prefs_12102 and copy/paste all the possible values from the 12c documentation for dbms_stats.get_prefs(). Then add a branch for your release and off you go.

Happy troubleshooting!