column owner format a20
set pagesize 100
select owner,to_CHAR(LAST_ANALYZED,'DD-MON-YY') "ANALYZED ON",
COUNT(*)
FROM DBA_TABLES 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(*)
-------------------- ---------- ----------
DBSNMP 30-JUN-05 15
OLAPSYS 30-JAN-12 47
XDB 30-JAN-12 2
MDSYS 13
OLAPSYS 12
WMSYS 30-JUN-05 34
OLAPSYS 30-JUN-05 67
WMSYS 2
EXFSYS 30-JUN-05 42
SYSMAN 23-JAN-12 204
DBSNMP 30-JAN-12 1
DMSYS 30-JUN-05 2
CTXSYS 30-JUN-05 36
XDB 30-JUN-05 8
MDSYS 30-JUN-05 32
SYSMAN 30-JAN-12 136
SYSMAN 1
WMSYS 30-JAN-12 6
XDB 23-JAN-12 1
SCOTT 23-JAN-12 4
DBSNMP 3
DBSNMP 23-JAN-12 3
ORDSYS 30-JUN-05 4
MDSYS 30-JAN-12 9
OUTLN 30-JUN-05 3
TSMSYS 23-JAN-12 1
EXFSYS 2
CTXSYS 30-JAN-12 1