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