Enable Tracing For A Particular Program
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Enable Tracing For A Particular Program

Hybrid View

  1. #1
    Join Date
    Mar 2004
    Location
    INDIA
    Posts
    25

    Enable Tracing For A Particular Program

    Hi,

    I am using the following LOGON trigger, in order to get the SQL TRACING enable for the session, which is currently running EXCEL.EXE Program.
    But in this way, the SQL TRACING for other sessions were also enable, when they logon to the database using other program rather than EXCEL.

    Because, we only need to enable the SQL TRACING, for the session which is running the EXCEL PROGRAM, not for other programs/sessions.

    I am looking forward to an advice .

    Chuck



    CREATE OR REPLACE TRIGGER testtrg
    AFTER LOGON ON DATABASE

    DECLARE

    v_sid number;
    v_serial# number;
    v_username VARCHAR2(30);
    v_program VARCHAR2(64);
    v_time date;

    BEGIN

    select username,sid,serial#,program,logon_time into v_username,v_sid,v_serial#,v_program,
    v_time from v$session s
    where sys_context('userenv','SESSIONID')=s.audsid and s.program='EXCEL.EXE';

    if (v_program = 'EXCEL.EXE') then

    execute immediate
    'exec dbms_system.set_sql_trace_in_session(v_sid,v_serial#,true)' ;

    END IF;
    else
    null;

    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    a point for you to consider - what happens when I rename excel.exe to i_am_cool.exe

    I can get past your trigger easily

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by davey23uk
    a point for you to consider - what happens when I rename excel.exe to i_am_cool.exe

    I can get past your trigger easily
    Actually not. Because the original poster has screwed his database even more than he (and you ) thinks. If you try what you suggested you won't be able to connect to his database at all. What will happen? An unhandled predefined exception NO_DATA_FOUND will be raised in the logon trigger, so you will be thrown out. In fact, noone with will be able to log on, unless his program represents itself like "EXCEL.EXE".

    So in fact, I think the original poster has not realy provided his/her actual code of logon trigger (or he/she didn't mention that suddenly noone is able to connect to the database unless the connection has been made by something that represents itself as "EXCEL.EXE" to the database).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    aahh the benefits of actually reading the code

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, the fact is that that trigger doesn't even get compiled, because of the syntactical errors!!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by jmodic
    Well, the fact is that that trigger doesn't even get compiled, because of the syntactical errors!!
    Picky picky picky. I can't stand these perfectionists.

    By the way, shouldn't it be "because of the syntax errors"?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    By the way, shouldn't it be "because of the syntax errors"?
    If you say so. Gee, I realy can't stand those bloody language purists...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Mar 2004
    Location
    INDIA
    Posts
    25

    ENABLE SQL TRACING FOR A PARTICULAR SESSION

    Hi Everyone,

    The logon trigger which I posted earlier has been compiled succesfully.
    The problem is that it enables SQL TRACING for all the session.
    However I wanted to enable sql tracing For a particular session which is using EXCEL.
    WHAT PART OF CODE I CHANGE IN ORDER TO ENABLE SQL TRACING FOR THAT PARTICULAR SESSION WHICH IS USING EXXCEL.
    Only comments from serious people.

    Thanks


    Chuck

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: ENABLE SQL TRACING FOR A PARTICULAR SESSION

    Originally posted by Chuck
    The logon trigger which I posted earlier has been compiled succesfully.
    No it hasn't. There's an obvious syntax error that prevents compilation.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Mar 2004
    Location
    India
    Posts
    4
    Hi,
    Try msqry32.exe instead of excel.exe

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