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

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