Find tables not accessed for past n months in Vertica database

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,
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

Sample results

sample results

There are no comments. Click here to write the first comment.