How to audit all SELECT statements for a specific schema?

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.
Sharp2bAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sharp2bAuthor Commented:
Thanks, but the solution there seem to be to use the trace files. In my case, these will be on a remote UNIX server and are also not that easy to look at.

Ideally, I just want to get the SELECT statements from a specific user logged to a table. I could then work more efficient like:
1. Make a test
2. Check the select statements (in many cases, just how many)
3. Purge the audit table
4. Back to 1...

0
sventhanCommented:
You can try this SQL. If wanted to keep the history you should populate the results of this SQL to a temp table.

select s.status, s.username, s.osuser, s.server, s.machine,
--utl_inaddr.get_host_address(SUBSTR(S.machine, INSTR(machine,'\')+1)) ip,
s.module, s.client_info, s.terminal,
s.program, p.program, s.logon_time, s.process, p.spid, p.pid, s.audsid, a.sql_text
from v$session s, v$process p, v$sqlarea a
where s.paddr = p.addr(+)
and s.sql_address = a.address
and s.sql_hash_value = a.hash_value
AND a.parsing_schema_id=(select user_id from all_users where username='SCOTT') <========= change the schema name  or comment this line for all the schema
and a.last_active_time >= to_date('14-DEC-2010 8:00:00 AM','dd-mon-yyyy hh:mi:ss am') <============= change dates according to your need
ORDER BY LAST_ACTIVE_TIME DESC
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sharp2bAuthor Commented:
OK, but I was under the impression this only gives the current or last SQL statement - No?

If this is the case, how can I make this run once after each sql statement is made so that it gets logged?

Maybe I was not entirely clear, the client makes several sql statements (via the middle tier service) to get the data it needs. If I make one query on the client, several select statements is sent to Oracle in the background. This could be some 10-100 statements.
0
sventhanCommented:
< OK, but I was under the impression this only gives the current or last SQL statement - No?
Execute the statement and see how far back you could go. Do you like to capture the live DATA or something executed in the past?

< If this is the case, how can I make this run once after each sql statement is made so that it gets logged?

If you need to do some trend analysis, you should keep the data logged. If you want you could use some free tools out there to do the job.


< Maybe I was not entirely clear, the client makes several sql statements (via the middle tier service) to get the data it needs. If I make one query on the client, several select statements is sent to Oracle in the background. This could be some 10-100 statements.

You can avoid duplicates by grouping the statements.  


0
sventhanCommented:
You could also query the following DMVs to get the historic info.

   dba_hist_sqlstat
   dba_hist_snapshot

http://www.dba-oracle.com/t_find_historical_sql_by_date.htm
0
sventhanCommented:
Use DBA_HIST_SQLTEXT to find query ran in past.

Or if you like to know all the command executed by particular session, then go for ACTIVE_SESSION_HISTORY as:-
SELECT  a.SESSION_ID, C.SQL_TEXT,
        B.NAME,
        COUNT(*),
        SUM(TIME_WAITED)
FROM    v$ACTIVE_SESSION_HISTORY A,
        v$EVENT_NAME B,
        v$SQLAREA C
WHERE   A.SAMPLE_TIME BETWEEN sysdate -1/24 AND sysdate AND
        A.EVENT# = B.EVENT# AND
--        A.SESSION_ID= 123 AND
        A.SQL_ID = C.SQL_ID
GROUP BY a.SESSION_ID, C.SQL_TEXT, B.NAME
order by a.SESSION_ID;

If you like to see all long running queries, then go for this:-
select * from v$session_longops where time_remaining > 0;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sharp2bAuthor Commented:
Thanks a lot for your help.

I'm getting some useful information out of this and learned some as well.

But at the end, I managed to convince my colleagues to go out of the database for a while so that I could bounce it and use:
ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

Then I can set:
AUDIT SELECT TABLE BY test_user BY ACCESS;
which will log all select statements to SYS.AUD$.

I can query this like:
select userid, NTIMESTAMP#, TO_CHAR(SQLTEXT), TO_CHAR(SQLBIND) from SYS.AUD$ where userid='TEST_USER'
order by NTIMESTAMP#;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.