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
Best Answer
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.
To disable this tracing, use the following statement.
Steps:
USER_DUMP_DEST
to find trace files.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.In this case, I am going to use service name.
Let's use the
trcsess
utility.We have created a single trace file and we can use
tkprof
utility to create formatted output from this trace file.Note: If you were using Enterprise Edition you could use fine-grained auditing which is not available in Standard Edition
References: