Page 1 of 1
When was a table last accessed and by whom?
Posted: Fri Jan 18, 2013 7:38 pm
by fsalvelt
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
Re: When was a table last accessed and by whom?
Posted: Fri Jan 18, 2013 8:08 pm
by jpcavanaugh
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
Re: When was a table last accessed and by whom?
Posted: Mon Jan 21, 2013 1:51 pm
by fsalvelt
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.
Re: When was a table last accessed and by whom?
Posted: Fri Feb 19, 2016 6:31 pm
by JimKnicely
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;