alter session set events

[RobertK]

6.0.6.947
10gR2

Does "alter session set events '10046 trace name context forever, level '" work in PL/SQL Developer ?

thanks

 

[Joachim Rupik]

Yes it works. To check if event was set you can use the following code (execute 'alter session' and the following code within the same window - session):

declare
  event_level number;
begin
  for event_number in 10000..10999 loop
    sys.dbms_system.read_ev(event_number, event_level);
    if (event_level > 0) then
      sys.dbms_output.put_line(
  'Event ' ||
  to_char(event_number) ||
  ' is set at level ' ||
  to_char(event_level)
      );
    end if;
  end loop;
end;

Below is a Browser Extender extension which will set 10046 or 10053 events for selected session from Sessions Window. Cut and paste into a file with .be extension. Works in version 2.5 and above of BE and unfortunately starting from version 7 of PLD.

PL/SQL Developer Browser Extender Command

[MAIN]
NAME=Session - Set event
CAPTION=Set event...
OTYPE=SessionWindow
OOWNER=%
ONAME=%
SEPARATOR_ABOVE=N
SEPARATOR_BELOW=N
LOAD_AND_EXECUTE=N
LOAD_COMMAND=Y
LOAD_AFTER=REPLACE
LOAD_WIN_TYPE=2
EXECUTE_AND_CLOSE=Y
PRIVILEGE_REQ=N
PRIVILEGE=
ORACLE_VERSION_REQ=N
ORACLE_VERSION=7.0.0
ONCLICK_CONFIRM=N
ONCLICK_CONFIRM_MSG=
ONCLICK_SPLASH=N
ONCLICK_SPLASH_MSG=
ONCLICK_SPLASH_MSG_BL=N
ONCLICK_SPLASH_MSG_AFTER=
ONCLICK_SPLASH_DELAY=0
ONCLICK_IGNORE_EXCEPTIONS=Y
ONPOPUP_IGNORE_EXCEPTIONS=Y

ONCLICK_CODE_TYPE=3
ONCLICK_EXTERNAL_CODE_FILE=
[ONCLICK]
\{$WINDOW TYPE = TEST\}
\{$COMMENT INFO = This extension will set an event with given level for selected session

Browser Extender dynamic template\}
begin
  -- set timed statistics at user session level if not set at system level.
  sys.dbms_system.set_bool_param_in_session(sid => #SID ,serial# => #SERIAL#, parnam => 'timed_statistics',bval => true);
  -- set max dump file size if not set at system level.
  sys.dbms_system.set_int_param_in_session(sid => #SID ,serial# => #SERIAL#, parnam => 'max_dump_file_size',intval => 10000000);
  -- set the event at level
  sys.dbms_system.set_ev(si=>#SID, se=>#SERIAL#, ev=>\{Event=SQL Trace (10046):10046,CBO trace (10053):10053,...\}, le=>\{Level=1,2,4,8,12,16,0\}, nm=>'');
end;

[ONPOPUP]