Pythian Blog: Technical Track

Oracle Database: Query to List All Statistics Tables

by Suresh Karthikeyan on 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
Topics: Oracle DBA Lounge Technical Track Technical Blog Pythian

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.