Recently Analyzed Table List

Two useful queries to get a list of recently analyzed tables. Can be used to check when the tables were last analyzed. Normally oracle should analyze tables on its own.

SELECT owner,
       SUM (DECODE (NVL (num_rows, 9999999), 9999999, 0, 1)) analyzed,
       SUM (DECODE (NVL (num_rows, 9999999), 9999999, 1, 0)) not_analyzed,
       COUNT (table_name) total
  FROM all_tables
 WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner;

SELECT table_name,
       TO_CHAR (last_analyzed, 'MM/DD/YYYY HH24:MI:SS') last_analyzed
  FROM user_tab_columns
 WHERE     last_analyzed IS NOT NULL
       AND column_id = 1
       AND (SYSDATE - last_analyzed) < 30
ORDER BY 2;