Add new comment

Question:
I want to get the information about when a table is queried in oracle. Is there any log in oracle which shows the queries. I was looking around v$sqlarea and v$sqltext but, the system admin does not allow me to reach those tables. - Dursan @ Stack Overflow

My answer:
In a default installation I know of no way to reliably get this info. You may be able to catch SQL Statements that were recently run in v$sql* views, but v$sql* views are transient in nature and are used to support normal operations of the database. Statements can age out so it is not a reliable way to audit.

What is a proper reliable way to get this info? Oracle Auditing. It contains the ability to record fine grained information about how your database objects are touched.

In this case you will want to investigate the AUDIT SELECT. After doing the basic config for auditing (usually done by a DBA) then SELECT auditing can be set up for specific tables like this:

AUDIT SELECT ON employees;

When a user SELECTS from employee, either directly or through a view, a record will be written to the audit trail (text file or SYS.AUD$ depending on configuration). The trail will have username, timestamp, table_name, and some other information to help you determine what the user was doing at the time.

Here is a 9i reference for auditing that gives an overview including info on AUDIT SELECT: Oracle 9i Auditing Documentation

Be aware that fine grained auditing can slow things down. Whatever you are auditing now has a new layer of activity that must be completed (writing to the audit trail). If you have a business need to know who sees what data that is understandable, but make sure to be aware of the performance implications.