Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Question Unanswered: retrieve SQL_TRACE value

    Hi
    In my JDBC implementation, I need to alter session's trace value sometimes.
    Befrore I alter the current session trace value, I need to know whether the current trace has been enabled or not. We know that trace can been set in ini.ora, if trace has been set in ini.ora, I do not need to enable trace at my JDBC connection. How can I retrieve the default SQL_TRACE value?
    Thanx
    Howard

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    select value from v$parameter where name = 'sql_trace'

    returns either TRUE or FALSE

    HTH

    CVM.

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    Hi
    Thank you very much.
    Another quesions is:
    Though I can get this from the V$parameter table. sometimes I do not know whether the current session's SQL_TRACE is true or not. Since I am using a connection pool, suppose client A gets a connection from pool and alters the session to SQL_TRACE false, even though the SQL_TRACE is true in default, the current SQL_TRACE will be false. After doing his queries, client A puts back the connection to pool. A minute later, client B gets the same conection from pool, but now this conection's SQL_TRACE is false although the default (in init.ora) is true.
    How can I get the SQL_TRACE value for current session?
    Thanks a lot.

  4. #4
    Join Date
    Oct 2003
    Posts
    4
    go to
    http://www.jlcomp.demon.co.uk/tracetst.html
    or look at this
    (at http://www.jlcomp.demon.co.uk/tracetst.html)

    JL Computer Consultancy
    Am I running SQL_TRACE

    --------------------------------------------------------------------------------

    SQL_TRACE is a well-documented facility in Oracle, and many developers are now including some sort of standard routine in their programs to allow then to switch sql_trace on selectively in production systems. (See my notes on a simple tracing package for example).

    One problem with such routines however is that of determining whether or not the session you are currently running is already in trace mode. Imagine you have flagged functions A and B to run with sql_trace switched on - but function A calls function B - in many systems function B will switch off sql_trace as the function ends because it did not know (or register) the fact that sql_trace had been switched on by the caller: the net result is that function A stops tracing right after it calls function B.

    It would be possible to produce a tidy work-around to this problem IF a function could determine whether or not sql_trace was already switched on. It is possible, and this note tells you how.

    In an undocumented feature of Oracle (actually it is documented - the documentation says: "This is an internally used routine that should never be used by users", so you have been warned) there is a function to set ANY event you want at any level you choose, and another to read the current level of the event. Since sql_trace is synonymous with event 10046 at level 1 you can, if you dare, use the latter function to find out whether your current session is running with sql_trace true.

    The relevant package is dbms_system in the script $ORACLE_HOME/rdbms/admib/dbmsutil.sql (you will find that this has been wrapped in Oracle 8 and transferred to script prvtutil.plb). If you have a version 7 copy of this script handy you will see that the procedure:

    dbms_system.set_sql_trace_in_session (
    id number,
    serial# number,
    sql_trace boolean
    )
    actually calls the procedure:

    dbms_system.set_ev(
    si binary_integer, -- sid
    se binary_integer, -- serial#
    ev binary_integer, -- event
    le binary_integer, -- level
    nm varchar2 -- name
    )
    and that the package also contains a procedure:

    dbms_system.read_ev(
    iev binary_integer,
    oev binary_integer
    );
    which is the one we are after. Even if you no longer have version 7 handy, if you 'describe dbms_system' from SQL*Plus in version 8 you will get the (complete) list of procedures and functions in the package with their lists of parameters.

    Finding out if sql_trace is running for your session is easy, you simply execute a PL/SQL block something like the following - and I have switched on sql_trace in this script so that I can give you a sample of the output.


    --------------------------------------------------------------------------------

    rem
    rem Script: read_event.sql
    rem Author: Jonathan Lewis
    rem Dated: 14th-Jan-1998
    rem Purpose: Call dbms_system to check sql_trace
    rem Returns the level of event 10046 for current session
    rem This will be 1 if sql_trace is true.
    rem
    set serveroutput on size 10000

    alter session set sql_trace true;

    declare
    iev binary_integer := 10046;
    oev binary_integer := 0;
    begin
    dbms_system.read_ev(iev,oev);
    dbms_output.put_line(oev);
    end;
    .
    /

    --------------------------------------------------------------------------------

    Sample output:

    SQL> start read_event

    Session altered.

    1

    PL/SQL procedure successfully completed.

    SQL>

    --------------------------------------------------------------------------------

    Addendum:

    Of course, now that you know how set_sql_trace_in_session works, you could call the set_ev procedure directly and set almost any event you fancy in any session you need to hit. One day, some time this millenium probably, I will be writing up the list of events that I have called upon in the past to solve problems or find out how Oracle works.

    Be warned that the package is not totally reliable. Test carefully on your platform and version of Oracle before firing off these functions too casually.

    Back to Main Index of Topics

  5. #5
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by happyaq
    Hi
    Thank you very much.
    Another quesions is:
    Though I can get this from the V$parameter table. sometimes I do not know whether the current session's SQL_TRACE is true or not. Since I am using a connection pool, suppose client A gets a connection from pool and alters the session to SQL_TRACE false, even though the SQL_TRACE is true in default, the current SQL_TRACE will be false. After doing his queries, client A puts back the connection to pool. A minute later, client B gets the same conection from pool, but now this conection's SQL_TRACE is false although the default (in init.ora) is true.
    How can I get the SQL_TRACE value for current session?
    Thanks a lot.
    As far as I understand the Oracle documentation, v$parameter contains the settings for your CURRENT session.

    I pasted an extract of the oracle documentation below

    >>>ORACLE DOCUMENTATION<<<
    V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.
    Column Datatype Description

    NUM NUMBER
    Parameter number


    NAME VARCHAR2(64)
    Name of the parameter


    TYPE NUMBER
    Parameter type:

    * 1 - Boolean
    * 2 - String
    * 3 - Integer
    * 4 - Parameter file
    * 5 - Reserved
    * 6 - Big integer


    VALUE VARCHAR2(512)
    Parameter value for the session (if modified within the session); otherwise, the instance-wide parameter value


    ISDEFAULT VARCHAR2(9) Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
    <<<ORACLE DOCUMENTATION>>>

    Regards,

    CVM.

  6. #6
    Join Date
    Oct 2003
    Posts
    4
    Got it.
    Thank you so much.
    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •