Oracle - SQL_TRACE

Card Puncher Data Processing


  • Set a trace identifier:
alter session set tracefile_identifier = 'myIdentifier';

The trace file identifier is added to the name of the trace files.

  • Set Trace on:
alter session set sql_trace = true;
  • Perform SQL Actions
select * from ...
select * from
  • Set off
alter session set sql_trace = false;
  • Where are the trace files?
show parameter user_dump_dest
grep "A little bit of my statement" *.trc
  • Formatting of the trace file with tkprof.
tkprof oracl_ora.....trc

Connection Pool

With a typical connection pool, a database session (the level at which we generally trace) is shared across multiple unrelated end-user sessions. Without a connection pool, the application would own a database connection, the level at which Oracle is built to “trace” at from start to finish.

With a connection pool, that one connection is shared by perhaps every end-user session in the system. We end up with a trace file that hase not only the trace information we are interested in, but the trace information from any-end user session that used the connection. The resulting trace file would be intermingled with my SQL, and with their SQL, and with everyone's SQL.

With a connection pool :

  • we must have the ability to turn on tracing for a specific user of for a specific module
  • when the connection pool grab a connection, it should issue alter session set sql_trace = true;
  • prior to returning the connection to the pool, it should issue set sql_trace = false;


Discover More
Card Puncher Data Processing
Oracle - Recursive Calls

Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called : recursive calls or recursive SQL statements. For example :...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database

Share this page:
Follow us:
Task Runner