/*
  This script will set the SQL Trace on in passed SID.
  
  
  CONN sys/password AS SYSDBA;  -- User must have SYSDBA.

  oradebug SETORAPNAME PR00     -- Debug process by name.
  ORADEBUG SETMYPID;            -- Debug current session.
  ORADEBUG SETOSPID 1234;       -- Debug session with the specified OS process.
  ORADEBUG SETORAPID 123456;    -- Debug session with the specified Oracle process ID.

  ORADEBUG TRACEFILE_NAME;      -- Display the current trace file.
  oradebug UNLIMIT              -- Set trace to unlimited size.

  ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;   -- Start SQL Trace
  ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;                 -- Stop SQL Trace 
  
  ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;   -- Start Optimizer Trace
  
  exec dbms_monitor.session_trace_enable (session_id=>123,serial_num=>123, waits=> TRUE, binds=> true );
  exec dbms_monitor.session_trace_disable (session_id=>123,serial_num=>123);

   -- Trace user's own session
  exec dbms_monitor.session_trace_enable (NULL,NULL,TRUE,TRUE) ;
  exec dbms_monitor.session_trace_disable ;   
  
  alter system set events '1031 trace name errorstack level 3';  
  
*/

BEGIN
   FOR i IN ( SELECT SID, serial#
               FROM v$session
              WHERE SID = &&1 )
   LOOP
      /* Set session DUMP file size */ 
      SYS.dbms_system.set_int_param_in_session ( i.SID
                                               , i.serial#
                                               , 'MAX_DUMP_FILE_SIZE'
                                               , 2097152                -- operating system blocks
                                               );
      /* Set session TIMED_STATISTICS to TRUE */ 
      SYS.dbms_system.set_bool_param_in_session ( i.SID
                                                , i.serial#
                                                , 'TIMED_STATISTICS'
                                                , TRUE
                                                );

      -- alter session  set events '10046 trace name context forever, level 12';
      /* Set session trace level to 12 */ 
      dbms_system.set_ev ( i.SID
                         , i.serial#
                         , 10046
                         , 12
                         , ''
                         );

      /* Set session SQL trace to TRUE */ 
      dbms_system.set_sql_trace_in_session ( i.SID
                                           , i.serial#
                                           , TRUE
                                           );

      /* To disable trace*/
      -- dbms_system.SET_BOOL_PARAM_IN_SESSION(i.SID,i.serial#,'SQL_TRACE',FALSE);

   END LOOP;
END;
/