Query below allows to find tables not accessed for n months (3 in this case).
with tab_access as ( select table_oid, max(time) as last_access from v_internal.dc_projections_used group by table_oid ) select t.table_schema, t.table_name, ta.last_access from v_catalog.tables t left join tab_access ta on t.table_id = ta.table_oid where last_access < CURRENT_TIMESTAMP - interval '3' month order by ta.last_access desc;
- table_schema - schema name containing table
- table_name - name of the table
- last_access - date of last access
- One row represents one table not accessed for past 3 months
- Scope of rows: all tables not accessed for past 3 months in database
- Ordered by last access time descending