Oracle Scratchpad

October 12, 2018


Filed under: Oracle — Jonathan Lewis @ 4:39 pm BST Oct 12,2018

I’ve been a long time (though occasional) user of the undocumented dbms_system package, typically using it to write messages or insert break lines in trace files (or the alert log). Thanks to an email from Cary Millsap I’ve recently discovered that the procedures for writing to trace files have been copied to a separate dbms_log package – which is nice because some of the things in dbms_system shouldn’t be made available to general code, for example the procedure kcfrms which resets a number of the “max time” columns in various dynamic performance views. It can be very useful occasionally – you might even want to call it just before or just after every AWR snapshot – but I’d rather that no-one else was able to call if I thought that I needed to  do so.

The dbms_log package is also (currently – even in 18.3) undocumented but maybe one day soon it will appear in the PL/SQL Packages and Types reference manual. The procedures available in the package are as follows:

SQL> desc dbms_log
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
  • ksdddt moves to a new line in the trace file writes the date and moves to the next line but won’t do anything if the trace file has not already been opened; so if you want a datestamp at the top of the output you actually have to start with a ksdwrt or ksdind call.
  • ksdfls flushes any pending writes to the trace file / alert log and closes the file – which isn’t relevant in my example but would make a difference to when you see the text that has been written from inside a pl/sql block.
  • ksdind writes an “indent” of lvl colon (‘:’) symbols to the trace file. This is a one-off effect, it doesn’t set an indent for all future lines it merely writes the ‘:’ so that the next call to ksdwrt appends its text after the colons.
  • ksdwrt writes tst to the trace file if dest = 1, to the alert log if dest = 2 and to both if dest = 3, adding a new-line at the end of the text.

Here’s a fragment of code calling the procedures to write something to my trace file:

execute dbms_log.ksdddt
execute dbms_log.ksdwrt(1,'Starting')
execute dbms_log.ksdddt
execute dbms_log.ksdwrt(1,'Underlining')
execute dbms_log.ksdind(20)
execute dbms_log.ksdwrt(1,'Indented')
execute dbms_log.ksdwrt(1,'Not Indented')
execute dbms_log.ksdind(30)
execute dbms_log.ksdddt
execute dbms_log.ksdwrt(1,'Finished')
execute dbms_log.ksdind(30)
execute dbms_log.ksdfls

Here’s the text that appears in the trace files:


*** 2018-10-04T16:31:15.525515+01:00 (ORCL(3))
Not Indented
*** 2018-10-04T16:31:15.532881+01:00 (ORCL(3))

Note how the call to ksdddt in line 1 of code didn’t write a date into the trace file because it wasn’t yet open. The call to ksdwrt in line 2 writes ‘Starting’ and moves to a new line so we get a blank line when the call to ksdddt in line 3 moves to a new line and writes the date. At line 5 we “indent 20”, so the ksdwrt at line 6 starts after the string of colons, then moves to a new line where the indent is not repeated. We indent again at line 8, which leaves us at the end of a line, so when we call ksdddt it moves to the start of the next line and writes the date there – we don’t get a blank line.

Footnote: when I saw Cary Millsap’s note I assumed that the procedures had been copied across in a recent version of Oracle; in fact dbms_log has been around since at least

Footnote 2: While checking my library for references to dbms_system I came across a script I’d used to create a local version of dbms_system that allowed me to execute a call to “dbms_system.set_bool_param_in_sesssion(‘#_SILVER_BULLET’, true)”. I used it at the IOUG conference in 2006 to demonstrate that if you set this “very hidden” parameter to true then some of your queries could run faster.  (What the call actually did was enable query rewrite and disable function-based indexes so that a special time-wasting index I’d created couldn’t get used.)



  1. Jonathan, I’ve opened two Oracle service requests that you and your audience might find interesting:

    SR 3-18305035791 : dbms_log.ksdwrt opens a new trace file if you’re not tracing, unlike dbms_log.ksdddt
    SR 3-18299547281 : v$session.sql_trace doesn’t work with dbms_monitor, either

    They might sound unrelated from just the titles, but the interrelationship between these two behaviors makes it tricky to instrument an application the way I’m now prescribing.

    Comment by Cary Millsap — October 23, 2018 @ 7:15 am BST Oct 23,2018 | Reply

    • Cary,

      Thanks for the update.
      It’s surprising how big an impact little inconsistencies can sometimes have.
      I hope Oracle Corp. will take your input as a good reason for fixing these up.

      Comment by Jonathan Lewis — October 23, 2018 @ 8:54 am BST Oct 23,2018 | Reply

  2. […] More details, with examples, in this blog note. […]

    Pingback by Logoff Triggers | Oracle Scratchpad — April 29, 2021 @ 10:17 am BST Apr 29,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by