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).

Query

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;

Columns

  • table_schema - schema name containing table
  • table_name - name of the table
  • last_access - date of last access

Rows

  • 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

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