(Picture by Shane Hauser, via Unsplash)
A fellow developer recently asked me if I could help him check if a bug had been fixed in production or not, because he could not trace (pun intented) recent changes. The problem consisted in 2 specific queries launched a gazillion times on an Oracle 12.1 database. Among other things, I wanted to get some traces, so I asked questions to better understand and narrow down what should be traced.
Unfortunately, I could not get precise details. I then remembered this video of Bjoern Rost I saw a long time ago :
— ArchBeatDev (@ArchBeatDev) 24 novembre 2016
Luckily, this application uses the service
NICE_SERVICE to connect to its highly-schema-consolidated database. I checked in
v$session what modules would match this
service_name : I only got
JDBC Thin Client with
action empty, but it was better than nothing.
The DBMS_MONITOR package turned out to be very useful in this case, with
serv_mod_act_trace_disable subprograms. Definitions and parameters of these subprograms can be found in the official documentation. In my case, I simply used them this way :
exec dbms_monitor.serv_mod_act_trace_enable('NICE_SERVICE','JDBC Thin Client'); [...] Let it run for a few minutes [...] exec dbms_monitor.serv_mod_act_trace_disable('NICE_SERVICE','JDBC Thin Client');
A huge amount of trace files were generated, I compiled them in a single file with trcsess :
trcsess output=my_compiled_trace_file.txt service=NICE_SERVICE *.trc
Use services, they are great !