DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Problems using dbms_trace

  1. #1
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Problems using dbms_trace

    I created the following function which
    almost works. The problem is that I would
    like to return the name of the file being
    created for this trace. Therefore I can
    call it from my application and easily
    track down the file that I am looking for.
    The problem is that the supplied function DBMS_TRACE.get_plsql_trace_runnumber does
    not work. Unless I am missing something.
    It always returns a 0. I was hoping to
    return either the directory and filename
    or at least just the number of the tracing
    session. I have a procedure that I am going
    to use to stop tracing.
    Any help would be appreciated.

    Code:
    CREATE OR REPLACE FUNCTION 
      --' $Header:
      --' Oracle supplied tracing levels. More than one can be used.
      --' trace_all_calls          constant integer := 1;  -- Trace calls/returns
      --' trace_enabled_calls      constant integer := 2;
      --' trace_all_exceptions     constant integer := 4;  -- trace exceptions
      --' trace_enabled_exceptions constant integer := 8;  -- (and handlers)
      --' trace_all_sql            constant integer := 32; -- trace SQL statements
      --' trace_enabled_sql        constant integer := 64; -- at PL/SQL level (does not invoke SQL trace)
      --' trace_all_lines          constant integer := 128; -- trace each line
      --' trace_enabled_lines      constant integer := 256;
    	p_enable_sql_trace(p_trace_level BINARY_INTEGER DEFAULT DBMS_TRACE.trace_all_calls)
    		RETURN BINARY_INTEGER
    AS
    	v_trace_file	VARCHAR2(200);
    	v_tracenumber	BINARY_INTEGER;
    BEGIN
      --' start trace data dumping in session
      --' the parameter is the sum of the above constants representing which
      --' events to trace
    	DBMS_TRACE.set_plsql_trace(p_trace_level);
    
    	v_tracenumber := DBMS_TRACE.get_plsql_trace_runnumber;
    
      --' Return the run-number
    	v_trace_file := 'The number of the trace file is: '||TO_CHAR(v_tracenumber);
    
    	RETURN v_trace_file;
    END p_enable_sql_trace;
    /

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    First of all, your function p_enable_sql_trace could not possibly work. It will compile, however will throw out an exception as soon as you will run it. Your function is declared as returning a number (binary_*******), however you are returning bunch of text with it ('The number of the trace file is: '||TO_CHAR(v_tracenumber))!

    The second thing is that you don't provide the database release you are using. (Maybe I'm realy a better mind reader than you, as you might find out later on, BTW ). Such basic information should be included in each and any problem description in order to get any usefull reply. Now in your case, this piece of information is particulary crucial!

    If you are using 8.1.5 then read no further, as I don't have any instance of this release handy, so I can't comment what might be wrong. However I don't think you are using 8.1.5, as GET_PLSQL_TRACE_RUNNUMBER function does not yet exists in DBMS_TRACE
    package in 8.1.5, AFAIK.

    So I'm relying on my mind-reading ability (remember?) and assume you're using 8.1.6 or higher. In this case I think you have two problems.

    The first one is you won't ever find any trace file created with DBMS_TRACE, because it simply doesn't write the trace information into the OS files - it writes it into data dictionary tables!

    Your second problem might be that you don't have those data dictionary tables created and so the trace facility can't write the trace information enywhere! What makes me belive this is the case with your example is the fact that in such situation DBMS_TRACE.get_plsql_trace_runnumber consistently returns 0, exactly the same output as you are observing. If this all is true in your case, then simply run $O_H/rdbms/admin/tracetab.sql as sys to create the required dictionary objects and the DBMS_TRACE.get_plsql_trace_runnumber will start returning you the correct runnumbers.
    Last edited by jmodic; 02-16-2003 at 12:19 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You are of course correct about my typo. I had it returning a VARCHAR2 but tried to change it to return a number. I am testing this procedure with 9iSR2, but it would be nice if I could get it working with 8.1.7.4. We do not support any version before 8.1.7.4. When I had it returning VARCHAR2 it was creating trace files in the trce directory. Although to be fair I did not look at the files very carefully. You are correct again when you say I should make sure that the trace tables exist and pull the information from there. I will try that. The funny thing is that when I logged a tar with Oracle they told me to just do an alter session set sql_trace=true. Which I think is odd. They also said that the function GET_PLSQL_TRACE_RUNNUMBER is for internal support only and that I should not use it. Although I'm not sure why. They sometimes give us what appears to be a great tool but then fail to provide the adaquate support to really make use of it. Thanks for the help. Brian.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I hope you realise that DBMS_TRACE is something compšletely different than SQL_TRACE!!!! Two totaly different things with different purposes.

    SQL_TRACE (wether it is activated at session level or at instance level) will record traces of SQL statements only - it will not record any details about executing your PL/SQL code (appart from SQL statements contained in your PL/SQL constructs). It will write the trace information into OS files and you would typicaly "decypher" those trace files with tkprof.

    DBMS_TRACE on the other hand is ment to trace the execution of your PL/SQL logic, not the sql statements (typicaly). Its output is recorded in database tables, it will not create any OS files (if they were created, they were certanly not created by the DBMS_TRACE itself! And you would retrive the trace information directly from dictionary tables, you don't use tkprof to interpret the results.

    In any case, try creating the dictionary tables by running TRACETAB.SQL and you should start getting the correct traceruns with your procedure.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I did run TRACETAB.SQL and I am experimenting with different trace levels to see what I am getting. Basically the situation is we are writing a central logging package. We can call this package and log anything that is worth logging. Dynamic sql, results of a query, anything that will help a support person to track down a given bug.
    We are going to keep the common messages in a table, for internationalization purposes. This message can include bind varaibles like table name column name, etc. When we call the package one of the parameters is going to be trace level. This looks at the message coming back from the database and depending on how much detail the user wants either logs the message or not. My plan was to setup this package to log everything even if a developer didn't log a particular sql statement. So the dbms_trace package seemed interesting. It would be nice if it gave me the actual sql. But even if ot only gives the me program flow that can be useful. I was also going to look at the DBMS_PROFILER. Of course all of this assumes that the necessary packages, tables and grants are in place. But the fact that this package is going to get called from every other procedure, function and package allows me to add in additional logic like tracing and profiling that can be turned on when needed. I'm not sure if we are going to actually do this. Part of what I need to do is prove that this extra level of tracing, or profiling is worth the effort.

Posting Permissions

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


Click Here to Expand Forum to Full Width