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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.