I'm working on some testing and debugging on a client application that access a middle tier server which has an Oracle database underneath. I don't have much control of what happens on the middle tier but I can clearly see that I get significantly different performance depending how I do things on the client. So, in order to get best performance, I want to be able to see exactly what SQL queries are generated for the database by my different actions on the client.
To put it simple, how can I monitor all SELECT statements issued by a specific user and/or in a specific schema? (I'm connected as TEST_USER which also owns all data tables used in this case.)
I had a look at using:
ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;
But unfortunately, I will not be able to bounce the database today and I will have the same issue each time I want to run these kind of tests.
I then found DBMS_FGA.add_policy which seem to be the way to go.
But, then it looks like I'm required to specify each table and column I want to audit.
I want to be able to look at any SQL statement issued by my TEST_USER, any ideas how to do this?
I guess I could also do some "ALTER SESSION set sql_trace..." but I rather have the SQL statements logged to a table as this is much more convenient in my environment. And it would also be easier to clear the audit table between each test.