Find the time when table was last accessed in Vertica database

Article for: SQL Server IBM Db2 Teradata

Query below shows tables with last access time to them.


System table used in this query, which stores history of projection access time, can be limited by time and/or allowed disk space.


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
order by ta.last_access desc;


  • table_schema - schema containing table
  • table_name - table name
  • last_access - datetime of last access to table if there was any, otherwise NULL


  • One row represents one table
  • Scope of rows: all tables in database
  • Ordered by last access time

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.