How to find which user is using which table

[amolik]

I have a centra database user Scott and application TOAD is installed on many client machines. The users connect to DB from TOAD user Scott. Is There any way that I can find out which user accessing which table. This is becoming difficult for me as all the users connect through Scott schema

Amol

[grjohnson]

Select SID, serial#, osuser from v$session;

To identitify the users sessions individually. You can link these through different views to determine what staements they are running. Also, if you have the DBA password, TOAD has the functionality to see what SQL the users are using.

Cheers

[petermag]

The above reply is a common reply, but it is not a complete one. It missed out a lot of session (especially application based) that connect and disconnect almost instantaneously, and as a result it is almost impossible to track their session ID.

The proper way to find out SQL issued by any user, is to turn on SQL Trace. In the trace files, which is generated based on session, you can find out the exact machine they come from. Neither Oracle auditing nor Log Miner has the ability to track the actual individual SQL issued.

Peter Teoh