Table Last Accessed

SarahL

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!