Oracle Database: Query to List All Statistics Tables

1 min read

Jun 25, 2014 12:00:00 AM

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: EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab'); 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

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Oracle Database: Query to List All Statistics Tables

Share this

Share this

More resources

The getMOSPatch V2 is here!

Oracle E-Business Suite: Virtual host names

Database migration: how to handle large tables

Ready to unlock value from your data?