I would like to list out the timestamp of tables which are accessed from my application recently. I would like to know at what time they were accessed so that I can pull which part of application accesses which table?
The query
select * from all_tab_statistics;
provides only the timestamp of deleted,updated,truncated details but not the timestamp of read details.
I am a normal user and not DBA. Hence I do not have access to all system tables.
If you are the application guy, it may be easier to configure the application to log activity directly from the app layer. Maybe configure some sort of verbose logging option?
The ALL_TAB_MODIFICATIONS table, which is what I think you mean, is not a reliable source for what you're trying to identify, as it's not updated immediately after a change is made.
For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur.
If logging via the application isn't a feasible approach, hopefully you can enlist the help of your DBA. Point him/her to the Auditing functionality within Oracle, specifically the section on Auditing SQL Statements. This should be available on both Standard and Enterprise Editions, and this is one way to identify when people are reading data from a table.
What you can do is specify an audit for a given DML command, and log that acitvity to the audit log. The Tutorial on that page will run you through the entire process, so I'm not going to go into specific syntax in this post.
A few words of warning though. If you have a lot of activity, you will want to be very particular about what you audit and for how long. You will want to make sure you setup the appropriate maintenance process to purge the log after a reasonable amount of time. A good script can be found here. Additionally, you will also want to ensure your audit table is moved out of the SYSTEM tablespace. To do that, you'll need to take advantage of the DBMS_AUDIT_MGMT package.
Auditing is not something I would ever recommend you setup quickly or without forethought. Be careful about what you collect as the logs can fill quickly, especially within environments under heavy activity.
