Trace column 1.3

trace_column.sql is a script that you can use to quickly trace (event 10046) a couple of sessions with the same properties. For example: suppose there are multiple sessions of a user called APP_USER, you can start the trace on all sessions with that username in one go.

These properties are sought in v$session, so you can enter any column_name in there and start traces on all sessions that have a certain value in that column. Version 1.3 of trace_column.sql now also shows you what values are in that column:

SQL> @trace_column.sql
Please give the column name in v$session: USERNAME

USERNAME                         COUNT(*)
-------------------------------- ----------
APP_USER                                  2
SYS                                       1

Please give the value you are looking for: APP_USER
Please give a trace identifier to easier find your trace file: APP_USER

The trace starts immediately, but it also generates a script to turn the trace off in /tmp:

ls -ltr /tmp |grep trace

-rw-r--r--. 1 oracle    oinstall    187 May 25 15:16 trace_USERNAME_APP_USER_on.sql
-rw-r--r--. 1 oracle    oinstall    135 May 25 15:16 trace_USERNAME_APP_USER_off.sql

Also the trace file gets a trace identifier. This didn’t work in previous versions. But in version 1.3 it should work. Well, it did at work. And on my own virtual machine it didn’t. Let me know what your experiences are.


About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning, Oracle scripts and tagged , , . Bookmark the permalink.

Leave a Reply

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