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


  • Why do you need this collection of statements?
    – miracle173
    Mar 9, 2017 at 5:51
  • @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
    – tdaget
    Mar 22, 2017 at 9:54

1 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';


  • 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'
    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';

    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



    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

    Not the answer you're looking for? Browse other questions tagged or ask your own question.