Oracle Database: Query to List All Statistics Tables

Posted in: Technical Track

If you were a big fan of manual database upgrade steps, perhaps you would have come across this step many times in your life while reading MOS notes, upgrade guides, etc.

Upgrade Statistics Tables Created by the DBMS_STATS Package
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:

In my experience, I found the statistics tables can be created from Oracle rdbms version 8i. So this step became part of the database upgrade documents until now. I also noticed the structure of the statistics table was the same until 10gR2 version, but Oracle had modified the structure marginally on 11g and 12c versions.

I have been using this single query to list all statistics tables that exist on a database, which can be still used despite changes on the table structure.

SQL> select owner,table_name from dba_tab_columns where COLUMN_NAME=’STATID’ AND DATA_TYPE= ‘VARCHAR2’;

Though this is not a critical step, it is required as a part of the post upgrade. Here is the small action plan to run the required command to upgrade all statistics tables.

Connect as SYS database user and run these steps:
SQL> set pages 1000
SQL> set head off
SQL> set feedback off
SQL> spool /home/oracle/stattab_upg.sql
SQL> select ‘EXEC DBMS_STATS.UPGRADE_STAT_TABLE(”’||owner||”’,”’||table_name||”’);’ from dba_tab_columns where COLUMN_NAME=’STATID’ AND DATA_TYPE= ‘VARCHAR2’;
SQL> spool off
SQL> @/home/oracle/stattab_upg.sql
SQL> exit

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Yet another Mechanical Engineer, who likes Oracle Database.

2 Comments. Leave new

Though quite old, would like to know how to identify the stat table that require to be upgraded ??


An objective of data normalization is to make the collection of relations neutral to the query statistics, where these statistics are liable to change as time goes by. Pls explain how, Thanks


Leave a Reply

Your email address will not be published. Required fields are marked *