When was a table last accessed and by whom?

Moderator: NorbertKrupa

Post Reply
User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

When was a table last accessed and by whom?

Post by fsalvelt » Fri Jan 18, 2013 7:38 pm

Good afternoon!

I work at a firm that would like to know when certain tables in our warehouse schema were last accessed and who did the accessing. Other than scouring the log file is there a way in Vertica to find out which tables a user ran SELECT statements against and when?

Thanks
Thank, Fred

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: When was a table last accessed and by whom?

Post by jpcavanaugh » Fri Jan 18, 2013 8:08 pm

To simply find the last access time for a table by user -- (any access, not just select).

Code: Select all

select table_name, max(time) as last_access, user_name from dc_projections_used where is_virtual = 'f' and table_schema not like 'v_%' group by table_name, user_name order by 2,3 desc;
Results are something like:

Code: Select all

      table_name       |          last_access          | user_name 
-----------------------+-------------------------------+-----------
 Exchange_Dimension    | 2013-01-17 21:37:10.746771+00 | dbadmin
 Settlement_Dimension  | 2013-01-17 21:37:10.75779+00  | dbadmin
 Split_Dimension       | 2013-01-17 21:37:10.768766+00 | dbadmin
 Trader_Dimension      | 2013-01-17 21:37:10.847301+00 | dbadmin
 Date_Dimension        | 2013-01-18 16:30:46.503517+00 | dbadmin
 Stock_Dimension       | 2013-01-18 16:30:46.503569+00 | dbadmin
 StockTransaction_Fact | 2013-01-18 16:30:46.503582+00 | dbadmin

User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Re: When was a table last accessed and by whom?

Post by fsalvelt » Mon Jan 21, 2013 1:51 pm

Thanks, jpcavanaugh!

About the dc_projections_used table, you said:
(any access, not just select)
There seems to be two distinct values for the io_type column of the dc_projections_used table.

Code: Select all

dbadmin=> select distinct io_type from dc_projections_used;
 io_type
---------
 output
 input
(2 rows)
Are these values for SELECT and INSERT statements?

Thanks for your help.
Thank, Fred

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: When was a table last accessed and by whom?

Post by JimKnicely » Fri Feb 19, 2016 6:31 pm

FYI... You can track the type of usage using the IO_TYPE column in PROJECTION_USAGE table. The values for IO_TYPE can be either 'input', for SELECT queries, or 'output' for INSERTs and COPY.

Code: Select all

SELECT anchor_table_name,
       io_type,
       MAX(query_start_timestamp) last_request_timestamp
  FROM projection_usage
 GROUP BY 1, 2
 ORDER BY 3 DESC;
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Administration”