Oracle trace all SELECTS

I need to do a task but I have no idea how to do it.

Here is the problem:

I have about 1000 tables on a Oracle Database and many processes.

Each process does one or more SELECT on one or many tables. Because it's almost impossible to look in the source code to find which process does which SELECT on which tables, I would like to have some kind of trigger on SELECT on every table. The idea is that I will launch the processes one by one to be able to see which tables will query.

I know that there is no trigger on SELECT, but is there anything else?

I need to do this in a one shot, just to recover开发者_C百科 the necessary info, it will not run every day.

You could activate auditing. You can audit all SELECT with:

AUDIT SELECT TABLE;

You can specify BY SESSION so that only one record will be written to the audit trail per table accessed per session.

Your AUDIT_TRAIL parameter must be set to either DB or OS. If it is set to DB, the audit trail will be written to the SYS.AUD$ table.

Assuming that you can map a "process" in your terminology to a particular Oracle session, you could trace the Oracle session. That would show you all the SQL statements executed by that session.

You could also potentially do a SQL*Net trace from whatever the client machine is (note that the "client machine" in a three-tier environment is the application server). A SQL*Net trace tends not to be nearly as easy to work with, however.