analysse

WITH stats_summary AS (

    SELECT 

        owner,

        COUNT(*) AS analyzed_tables,                  -- tables with any stats

        COUNT(CASE WHEN last_analyzed > SYSDATE - 2 

                   THEN 1 END) AS recent_tables,      -- tables analyzed in last 2 days

        MAX(last_analyzed) AS last_analyzed_max

    FROM all_tab_statistics

    WHERE last_analyzed IS NOT NULL

      AND owner NOT LIKE 'SYS%'

      AND owner NOT LIKE 'APEX%'

      AND owner NOT IN ('SYSTEM','OUTLN','DBSNMP','CTXSYS','MDSYS','XDB')  -- typical exclusions

    GROUP BY owner

),

total_tables_per_schema AS (

    SELECT 

        owner,

        COUNT(*) AS total_tables

    FROM all_tables

    WHERE owner NOT LIKE 'SYS%'

      AND owner NOT LIKE 'APEX%'

      AND owner NOT IN ('SYSTEM','OUTLN','DBSNMP','CTXSYS','MDSYS','XDB')

    GROUP BY owner

)

SELECT 

    s.owner                          AS schema_name,

    t.total_tables,

    s.analyzed_tables,

    s.recent_tables,

    ROUND(s.analyzed_tables * 100 / NULLIF(t.total_tables, 0), 1) AS pct_analyzed_overall,

    ROUND(s.recent_tables  * 100 / NULLIF(t.total_tables, 0), 1) AS pct_analyzed_recent,

    TO_CHAR(s.last_analyzed_max, 'DD-MON-YYYY HH24:MI:SS') AS last_analyzed_max

FROM stats_summary s

JOIN total_tables_per_schema t ON s.owner = t.owner

WHERE s.last_analyzed_max > SYSDATE - 2          -- only schemas touched in last 2 days

ORDER BY s.last_analyzed_max DESC;