Posted by : Arjun Lagisetty Wednesday, April 12, 2017

When we have performance problem we often have to retrieve certain data for multiple tables for example row counts, last analyzed date from multiple databases. In an effort to simply that I create the following query which spools following details to the file c:\temp\tabledetailslist.txt. 

If you want to capture the same details for multiple databases run this SQL on each database, you should see all the results in one file


  1. Row count of each table 
  2. Last analyzed date of each table
  3. Number of Indexes on each table
  4. List of all indexes names on the table
SPOOL C:\temp\countlist.txt APPEND;

SET PAGESIZE 1000;
BREAK ON DATABASE;

  SELECT TRIM (SYS_CONTEXT ('userenv', 'instance_name')) AS DATABASE,
         TRIM (OWNER)                                  AS OWNER,
         TRIM (TABLE_NAME)                             AS TABLE_NAME,
         LAST_ANALYZED,
         TO_NUMBER (
             EXTRACTVALUE (
                 XMLTYPE (
                     DBMS_XMLGEN.GETXML (
                            'select count(*) c from '
                         || OWNER
                         || '.'
                         || TABLE_NAME)),
                 '/ROWSET/ROW/C'))
             ROW_COUNTS,
         TO_NUMBER (
             EXTRACTVALUE (
                 XMLTYPE (
                     DBMS_XMLGEN.GETXML (
                            'SELECT COUNT(1) C from ALL_INDEXES WHERE TABLE_OWNER =  '''
                         || OWNER
                         || ''' AND TABLE_NAME= '''
                         || TABLE_NAME
                         || '''')),
                 '/ROWSET/ROW/C'))
             NUM_INDEXES,
         EXTRACT (
             XMLTYPE (
                 DBMS_XMLGEN.GETXML (
                        'SELECT INDEX_NAME ||''; '' C from ALL_INDEXES WHERE TABLE_OWNER =  '''
                     || OWNER
                     || ''' AND TABLE_NAME= '''
                     || TABLE_NAME
                     || '''')),
             '/ROWSET/ROW/C/text()')
             INDEX_NAMES
    FROM ALL_TABLES
   WHERE TABLE_NAME IN ('TAB1',
                        'TAB2',
                        'TAB3',
                        'TAB4')
ORDER BY TABLE_NAME;

SPOOL OFF;

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty