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
- Row count of each table
- Last analyzed date of each table
- Number of Indexes on each table
- 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;
