DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: dbms_system.set_ev

  1. #1
    Join Date
    Jun 2007
    Posts
    60

    dbms_system.set_ev

    I run
    EXECUTE SYS.dbms_system.set_ev (42, 45529,10046,12,'');
    or
    EXECUTE SYS.dbms_system.set_sql_trace_in_session (42, 45529, TRUE);

    PL/SQL procedure successfully completed.

    and no trace files are generated in ../udump

    I am quite sure that I supply correct sid and serila#
    Any ideas ?

    Thanks

  2. #2
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    Check the type of connection of sid 42 in v$session. If it's SHARED then no trace file will generate by dbms_system.set_sql_trace_in_session.

  3. #3
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Hi

    work out the following.....
    "GRANT EXECUTE ON DBMS_SYSTEM TO username;"
    user you want to trace..
    then execute the procedure.
    gopu
    Last edited by gopu_g; 08-24-2007 at 02:25 AM.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Quote Originally Posted by gopu_g
    Hi

    Grant execute on the user you want to trace.
    GRANT EXECUTE ON DBMS_SYSTEM TO username;
    then execute the procedure.
    gopu
    I wouldnt grant execution privilege on a system package such as this one to a user

  5. #5
    Join Date
    Jun 2007
    Posts
    60
    No it does not work.

    The strange thing is that it worked before on a different systems.

    BTW this is oracle 10.2.0.2.0

    Any more ideas ...
    I run it from the script

    This is the way I get sid&serial num


    select a.username,a.sid ,a.serial#
    into :v_username,:v_sid,:v_serial_num
    from v/$session a, v/$sqlarea b, v/$process c
    where a.sql_address=b.address
    and c.ADDR=a.PADDR
    and c.SPID='$PID';

  6. #6
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    Quote Originally Posted by zam
    No it does not work.

    The strange thing is that it worked before on a different systems.

    BTW this is oracle 10.2.0.2.0

    Any more ideas ...
    I run it from the script

    This is the way I get sid&serial num


    select a.username,a.sid ,a.serial#
    into :v_username,:v_sid,:v_serial_num
    from v/$session a, v/$sqlarea b, v/$process c
    where a.sql_address=b.address
    and c.ADDR=a.PADDR
    and c.SPID='$PID';
    Have you check connection type of session whether it is SHARED or DEDICATED in v$session.

  7. #7
    Join Date
    Jun 2007
    Posts
    60
    Yes I did check - it's dedicated. I am testing my script and running my owen session for that.

  8. #8
    Join Date
    Jun 2007
    Posts
    60
    I am definetly missing something
    I have just run the same on the system it worked before and got the same result

    1. login as sysem in db and run sql - select count(*) from dba_objects a, dba_objects b; just to run for a while
    2. login as sysdba from different session
    3. Get SID and SERIAL#
    4. EXECUTE SYS.dbms_system.set_sql_trace_in_session (14, 470, TRUE);
    PL/SQL procedure successfully completed.
    5. Checked udump dir .. -nothing there

  9. #9
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    Check the user_dump_dest. (show parameter user_dump_dest)

    If not defined in parameter file then you have to check at $ORACLE_HOME/rdbms/trace folder

  10. #10
    Join Date
    Jun 2007
    Posts
    60
    Sorry ...
    Pando was right I have to GRANT EXECUTE ON DBMS_SYSTEM to user.
    I did it first and it didn't work for me probably just was looking not in a right place. Now it works
    Thanks !

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