How to capture all user's query on Oracle database

Question

I have an Oracle Database installed over Unix system. On this single instance database I have a lot of sessions opened from an application pool (application is installed on another server). Every sessions are opened with the same user name. As sessions can be closed by the pool (really, it can be) I can't capture session id and - for each - enable query trace functionality. The application is a closed source code (and proprietary), so I can't (and don't want to) reverse engineering it.

Now, I need to capture every query that this user execute. Ideally I want to get one file by user session (but it will be perfect if I get one file with all queries). My real goal is to get queries but session_id and/or query's timestamp could be welcome.

Important note: as my database is a Standard Edition I have no access to any Oracle advanced pack as "Oracle Tuning Pack", "Oracle Diagnostic Pack" or so on ... I only have access to the StatPack and regular SQL query over all database dictionary objects (as a sysdba could be).

Platform information:

• Unix RedHat 6.x
• Oracle Database 11g Standard edition
• only one database instance
• number of sessions opened between 150 and 250

Thanks for your ideas

Thomas

• miracle173: Why do you need this collection of statements?
• tdaget: @miracle173 I need this for two reasons: 1) I want to be able to analyse each query done by this application (for example for missing index analyse) 2) I want to be able to replay all query and transactions on a test database

Answer 1

As per my knowledge, there is no straightforward way to achieve this goal. However, I have tried to compose the following steps to fulfill the requirements.

You can enable trace for all sessions by using the following statement.

alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

To disable this tracing, use the following statement.

alter system set events '10046 trace name context off';

Steps:

First, enable 10046 event trace.

SQL> conn / as sysdba
SQL> alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

To get the names of these trace file use the following query where username should be your username used by the application.

SQL> SELECT LISTAGG(tf, ' ') WITHIN GROUP (ORDER BY tf) "Trace_List"
FROM (
 SELECT sys_context('userenv','instance_name') || '_ora_'|| p.spid || '.trc' as tf FROM 
 v$process p join v$session s 
 ON (s.paddr=p.addr) 
 WHERE s.username='JAY'
);
Trace_List
--------------------------------------------------------------------------------
orcl_ora_3443.trc orcl_ora_3458.trc orcl_ora_3473.trc

After that, go to USER_DUMP_DEST to find trace files.

SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Now, you have got multiple trace files scattered on your user dump dest. In order to combine and create a single trace file, you can use trcsess utility, which allows trace information from multiple trace files to be identified and consolidated into a single trace file. You can consolidate these trace files based on following criteria. Choose the which is common for these all sessions.

Session Id

Client Id

Service name

Action name

Module name

In this case, I am going to use service name.

SQL> select service_name from v$session where username='JAY';
SERVICE_NAME
----------------------------------------------------------------
SYS$USERS
SYS$USERS
SYS$USERS

Let's use the trcsess utility.

[server1@oracle ]$ trcsess output=/home/oracle/Desktop/main_trace_file.trc service='SYS$USERS' orcl_ora_3443.trc orcl_ora_3458.trc orcl_ora_3473.trc

We have created a single trace file and we can use tkprof utility to create formatted output from this trace file.

[server1@oracle ]$ tkprof main_trace_file.trc main_formatted_trace.txt

Note: If you were using Enterprise Edition you could use fine-grained auditing which is not available in Standard Edition

References:

TRCSESS

TKPROF