(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 :

Video: Use More Than One Service Per #Database. #2MTT by @brost #kscope2016 https://t.co/iK2wR4IEOJ pic.twitter.com/RRaA0dWMuz

— 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_enable and 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

You can find more information about trcsess in the official documentation and My Oracle Support.

Use services, they are great !