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
When was a table last accessed and by whom?
Moderator: NorbertKrupa
When was a table last accessed and by whom?
Thank, Fred
-
- 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?
To simply find the last access time for a table by user -- (any access, not just select).
Results are something like:
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;
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?
Thanks, jpcavanaugh!
About the dc_projections_used table, you said:
Are these values for SELECT and INSERT statements?
Thanks for your help.
About the dc_projections_used table, you said:
There seems to be two distinct values for the io_type column of the dc_projections_used table.(any access, not just select)
Code: Select all
dbadmin=> select distinct io_type from dc_projections_used;
io_type
---------
output
input
(2 rows)
Thanks for your help.
Thank, Fred
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: When was a table last accessed and by whom?
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.