Show last access time of a table in Oracle DB

Question

I'm using Oracle RDBMS for database. I'm trying to clean up the database and want to know if certain tables are even used any more. I require the last accessed time of these tables to reach a conclusion.

Please note that I need the timestamp of the latest SELECT query. I've solutions for finding timestamp of other activities like UPDATE, ALTER, etc. I'm looking specifically for SELECT timestamp.

Any help is appreciated.

Answer 1

You could use AUDIT(traditional auditing):

AUDIT SELECT ON table_name;

Answer 2

Yes, @lad2025 is right, I'd use AUDIT to find out who is accessing the tables.

To switch it on, just use:

AUDIT SELECT ON scott.emp  BY ACCESS;
AUDIT SELECT ON scott.dept BY ACCESS;  

You can then see which tables are audited by querying:

SELECT * 
FROM DBA_OBJ_AUDIT_OPTS 
WHERE owner='SCOTT';

OWNER OBJECT_NAME OBJECT_TYPE SEL
SCOTT DEPT        TABLE       A/A
SCOT  EMP         TABLE       A/A

If somebody selects from one of the audited tables, it appears in the audit trail:

SELECT * FROM scott.emp;

SELECT os_username, username, obj_name, action_name, timestamp
  FROM DBA_AUDIT_TRAIL 
 WHERE timestamp BETWEEN SYSDATE-7 AND SYSDATE
   AND owner = 'SCOTT' 
   AND action=3
 ORDER BY timestamp DESC;

OS_USERNAME USERNAME OBJ_NAME ACTION NAME TIMESTAMP
xxxx        yyy      EMP      SELECT      2018-05-30 08:01:07

To switch it off again, use:

NOAUDIT ALL ON scott.emp;
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER='SCOTT';

OWNER OBJECT_NAME OBJECT_TYPE SEL
SCOTT DEPT        TABLE       A/A

Be cautious, though. One row is added to the audit trail for each SELECT, so watch it carefully, it can generate a lot of data very quickly.