Display Schema Statistics Analysis History

SET ECHO OFF
SET FEEDBACK ON
SET PAGES 1000
SET LINES 132

REM
REM || Title : show_anal_history.sql
REM ||
REM ||
REM || Purpose : This script reports on which and when schemas were analyzed.
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : sys
REM ||
REM ||

SPOOL show_anal_history.lst

PROMPT
PROMPT Database Name
PROMPT

SELECT name "Database",
SUBSTR (TO_CHAR (SYSDATE, 'DD-MON-YYYY'), 1, 13) "Date"
FROM v$database
/

PROMPT
PROMPT Analyzed vs. Not analyzed tables
PROMPT

SELECT owner "Owner",
COUNT (num_rows) "Analyzed",
COUNT (*) - COUNT (num_rows) "Not analyzed"
FROM dba_tables
GROUP BY owner
/

BREAK ON "Date"

PROMPT
PROMPT Last analyze done on tables
PROMPT

SELECT TRUNC (last_analyzed) "Date",
owner "Owner",
COUNT (distinct table_name) "Tables"
FROM dba_tab_columns
WHERE last_analyzed IS NOT NULL
GROUP BY TRUNC (last_analyzed),
owner
/

SPOOL OFF

SET PAGES 80
SET FEEDBACK ON
CLEAR BREAKS
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.