Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Table Last Accessed

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!
0
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.