Tracing procedure
Does it happen to you guys to be approached by Java developers and asked to trace some session and then have to track the session and the trace file on the db server? Well, I got tired of being driven away from my things for this and wrote a simple framework that starts the tracing and displays the content of the trace file as an output parameter in the guy’s sql window.
Let’s see how it works in a practical case: the developer comes and says he has two db connections open, a Java one and a SQL Developer one, and he would want the Java one traced so he can check some parameters. Okay. You tell him to call in SQL Developer a start_tracing procedure, do his work and then a show_trace_file procedure and voilla, he’ll have the content of the trace file printed:
system.start_tracing(pi_schema_name => 'DEVELOPER_SCHEMA', pi_program => 1, po_trace_cursor => po_trace_cursor);
system.show_trace_file(pi_schema_name => 'DEVELOPER_SCHEMA', pi_program => 1, po_trace_cursor => po_trace_cursor);
I added the pi_program parameter in order to allow the developer to catch different sessions run under his user using different programs. These are the programs supported:
1 = java.exe
2 = JDBC Thin Client
3 = SQL Developer version 2.0 or above
4 = SQL Developer version 1.5.5
5 = SQL Developer version 1.5
7 = Toad
8 = PL/SQL Developer
9 = sqlplus
The code behind this framework is very simple:
- a directory mapped to the folder where Oracle keeps the trace files, in 11g this is [orahome]\base\diag\rdbms\[servicename]\[servicename]\trace
- a type and a pipeline function used to parse the trace files
- the start_tracing procedure used to track the session and start tracing
- the show_trace_file procedure used to display the content of the trace file
The script that creates the objects can be downloaded from here.