Friday, 4 July 2025

Filled under:

 SELECT 

    t.owner,

    t.table_name,

    ROUND(t.blocks * 8 / 1024, 2) AS size_mb,

    MAX(s.END_TIME) AS last_access_time

FROM dba_tables t

JOIN dba_hist_seg_stat_obj o

    ON t.owner = o.owner AND t.table_name = o.object_name

JOIN dba_hist_seg_stat s

    ON o.obj# = s.obj#

WHERE t.owner = 'YOUR_SCHEMA'

  AND REGEXP_LIKE(t.table_name, 

       '(_BAK$|_TMP$|_COPY$|_OLD$|_TEST$|_ARCH$|_STAGE$|_SNAP$|_DUP$|^TMP_|^STG_|^BK_|^OLD_|^NEW_|^TEST_)',

       'i')

GROUP BY t.owner, t.table_name, t.blocks

ORDER BY last_access_time DESC NULLS LAST;


0 comments:

Post a Comment