Table Last Accessed

Posted July 24, 2018 by Sarah Lemaire, Manager, Vertica Documentation

white cloud in vault type room representing cloud computing
Jim Knicely authored this post. You can query the data collector table DC_PROJECTIONS_USED to ascertain when a table was last accessed and by whom. Example: dbadmin=> SELECT table_name, dbadmin-> MAX(time) AS last_access, dbadmin-> user_name dbadmin-> FROM dc_projections_used dbadmin-> WHERE table_schema = 'public' dbadmin-> AND table_name = 'big_table' dbadmin-> GROUP dbadmin-> BY table_name, dbadmin-> user_name; table_name | last_access | user_name ------------+-------------------------------+----------- big_table | 2018-07-19 11:22:19.170055-04 | dbadmin (1 row) dbadmin=> SELECT sysdate FROM public.big_table LIMIT 1; sysdate —————————- 2018-07-23 11:15:50.502581 (1 row) dbadmin=> SELECT table_name, dbadmin-> MAX(time) AS last_access, dbadmin-> user_name dbadmin-> FROM dc_projections_used dbadmin-> WHERE table_schema = ‘public’ dbadmin-> AND table_name = ‘big_table’ dbadmin-> GROUP dbadmin-> BY table_name, dbadmin-> user_name; table_name | last_access | user_name ————+——————————-+———– big_table | 2018-07-23 11:15:50.505503-04 | dbadmin (1 row) Have fun!