column owner format a20
set pagesize 100
SELECT owner,
TO_CHAR(LAST_ANALYZED,'DD-MON-YY') "ANALYZED ON",
COUNT(*)
FROM DBA_indexes
WHERE OWNER NOT IN ('SYS','SYSTEM','PERFSTAT')
GROUP BY OWNER,
TO_CHAR(LAST_ANALYZED,'DD-MON-YY');
Sample Output:
Code: Select all
OWNER ANALYZED O COUNT(*)
-------------------- ---------- ----------
TSMSYS 1
DBSNMP 30-JUN-05 8
XDB 30-JAN-12 17
MDSYS 32
OLAPSYS 4
DBSNMP 27-JAN-12 1
WMSYS 30-JUN-05 52
OLAPSYS 30-JUN-05 135
SYSMAN 27-JAN-12 2
WMSYS 10
EXFSYS 30-JUN-05 36
SYSMAN 23-JAN-12 215
XDB 335
DBSNMP 30-JAN-12 1
DMSYS 30-JUN-05 2
CTXSYS 30-JUN-05 45
CTXSYS 1
XDB 30-JUN-05 33
MDSYS 30-JUN-05 44
SYSMAN 30-JAN-12 180
SYSMAN 29
DBSNMP 2
WMSYS 30-JAN-12 4
SCOTT 23-JAN-12 2
OUTLN 26-JAN-12 1
WMSYS 27-JAN-12 1
ORDSYS 30-JUN-05 4
MDSYS 27-JAN-12 1
MDSYS 30-JAN-12 6
OUTLN 30-JUN-05 3
OUTLN 1
TSMSYS 23-JAN-12 1
EXFSYS 3
CTXSYS 30-JAN-12 1