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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.