Query to check the timestamp of a table accessed recently

oracle

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.

Best Answer

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.

Related Solutions

Oracle 10g weird behaviour

The "noop" would have fired any related triggers on the table, and it would have advanced the system change number.

Oracle 10XE: Stop schemas seeing the all_users table

You have a few options, some of which will make your database unsupportable by Oracle (if you care!) as you need to fiddle with SYS owned objects.

The first option is to revoke select privs from everyone:

revoke select on all_users from public;

Then grant it back to certain users, plus all other internal Oracle users.

The 2nd option is to look at the source for the view, drop it, then recreate an altered version that supports your needs.

Another option that would trick casual users would be to create a table called ALL_USERS in each schema. Unless the logged in person explicitly qualified ALL_USERS (ie: SYS.ALL_USERS) they would get the resultset from the table in their own schema. This trick could also be done with a synonym pointing to a view that does:

select * from all_users where username = (SELECT sys_context('USERENV', 'SESSION_USER') from DUAL);

Not much else you can do with XE I'm afraid.

Best Answer

Related Solutions

Oracle 10g weird behaviour

Oracle 10XE: Stop schemas seeing the all_users table

Related Question