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
