I have been searching on metalink for the dbms_support packages as they are
apparently no longer supported in 8i. After talking to Oracle support they
suggest I use the dbms_system package.  (I thought this wasn't supported but
anyway)  Well I decided to create a wrapper that would handle this for me.
I have included the code as some people here thought it was useful, so I
thought perhaps some of you might as well.

If you decide to use the code great but please review it and make sure it
will work in your environment!  If you like it send me an email, if people
decide to make changes to the package I would appreciate knowing what you
have done to make it better.

Thanks,
Scott Watson.

CREATE OR REPLACE package ob_system IS
--------------------------------
-- PURPOSE
--  This procedure allows for various system functions.
--
-- DEPENDENCIES
--   - Package SYS.DBMS_SYSTEM  (Create a synonym and grant execute to owner
of this package).
--   - V$SESSION and V$PROCESS read Access
--   - Most effective when server output is on in SQL PLUS.
--
-- Access
--   This package should be restricted to the dba
--

-- Of course I take no responsibility if this doesn't work on your instance.
--
-- TESTED ON.
--   Oracle 8.1.6.3 .
--------------------------------

  -- Will display a listing of the current sessions
  procedure showSessions(cUserName varchar2 DEFAULT NULL, bBackGround
boolean DEFAULT FALSE);

  -- Turn tracing on for a various session
  procedure traceSession(sid BINARY_INTEGER, serial BINARY_INTEGER, binds
boolean DEFAULT TRUE, waits boolean DEFAULT FALSE);

  -- If the connection is persistent we can show a list of all the sessions
we have turned tracing on for
  procedure showTracedSessions;

  -- Stop a trace
  procedure endTrace(sid BINARY_INTEGER, serial BINARY_INTEGER);

  -- Stops all traces that we have stated
  procedure endAllTraces;

End;
/

CREATE OR REPLACE package body ob_system IS

-- Private data .
  TYPE trace_rec is RECORD
    ( serial binary_integer,
   waits  boolean,
   binds  boolean);

  TYPE Session_t is table of trace_rec index by binary_integer;

  sessions   Session_t;                   -- Create a table to hold our
sessions we have traced
                                          -- Sid will be our index and we
will store the serial#.

-- Implemented procedures.

  -- Will display a listing of the current sessions
  procedure showSessions(cUserName varchar2 DEFAULT NULL, bBackGround
boolean DEFAULT FALSE) IS
     cursor cNoBackground is
    select a.sid, a.serial#, nvl(a.username,'UNKNOWN') username,
nvl(a.osuser,'UNKNOWN') osuser,
           nvl(a.program,'UNKNOWN') program, a.type, NVL(b.spid,-1) spid
      from v$session a, v$process b
  where a.paddr = b.addr(+)
    and (a.username like '%' || cUserName || '%' or a.username is null)
    and a.type != 'BACKGROUND';

     cursor  cBackground is
    select a.sid, a.serial#, nvl(a.username,'UNKNOWN') username,
nvl(a.osuser,'UNKNOWN') osuser,
           nvl(a.program,'UNKNOWN') program, a.type, NVL(b.spid,-1) spid
      from v$session a, v$process b
  where a.paddr = b.addr(+)
    and (a.username like '%' || cUserName || '%' or a.username is null);

  rec_b  cBackground%rowtype;            -- structure to hold our data
   rec_nb cNoBackground%rowtype;            -- structure to hold our data
  rowcount integer;

  begin

      -- initialize our row count
      rowcount := 1;

   dbms_output.put_line(rpad('ID',5)||'SID,SERIAL#  ' ||
RPAD('OS/ORACLE',30) || RPAD('PROGRAM',25) || RPAD('OSPID',8) || 'TYPE');
      -- Open the cursor we need to find the data we are looking for
      if bBackGround then
       open cBackground;
       LOOP
           FETCH cBackGround INTO rec_b;
        EXIT WHEN cBackGround%NOTFOUND;
        dbms_output.put(lpad(rowcount,2) || '.. ' || RPAD('(' || rec_b.sid
||','|| rec_b.serial# ||')', 13) );
        dbms_output.put_line(RPAD(rec_b.osuser ||'/'|| rec_b.username,30) ||
RPAD(rec_b.program,25) || RPAD(rec_b.spid,8) || rec_b.type);
        rowcount := rowcount + 1;
       END LOOP;
       close cBackGround;                             -- close our cursor
   else
       open cNoBackground;
       LOOP
           FETCH cNoBackGround INTO rec_nb;
        EXIT WHEN cNoBackGround%NOTFOUND;
        dbms_output.put(lpad(rowcount,2) || '.. ' || RPAD('(' || rec_nb.sid
||','|| rec_nb.serial# ||')', 13) );
        dbms_output.put_line(RPAD(rec_nb.osuser ||'/'|| rec_nb.username,30)
|| RPAD(rec_nb.program,25) || RPAD(rec_nb.spid,8) || rec_nb.type);
        rowcount := rowcount + 1;
       END LOOP;
       close cNoBackGround;                           -- close our cursor

   end if;

  end;

  -- Turn tracing on for a various session
  /* NOTES
    The trace level can have the following values:

     1  Standard SQL_TRACE functionality
     4  As level 1 plus tracing of bind variables
     8  As level 1 plus wait events
    12  As level 1 plus bind variables and wait events.
nm Event name Eg: 'ERRORSTACK' for an errorstack, '' for context forever
  */
  procedure traceSession(sid BINARY_INTEGER, serial BINARY_INTEGER, binds
boolean DEFAULT TRUE, waits boolean DEFAULT FALSE) IS
     traceRec trace_rec;
  begin

  if (binds AND waits) then
     dbms_system.set_ev(sid,serial,10046,12,'');
  elsif (binds) then
      dbms_system.set_ev(sid,serial,10046,4,'');
  elsif (waits) then
      dbms_system.set_ev(sid,serial,10046,8,'');
  else
     dbms_system.set_ev(sid,serial,10046,1,'');
     end if;

  traceRec.serial := serial;
  traceRec.binds := binds;
  traceRec.waits := waits;

  -- Add the sid to the list of sessions we are tracing.
  sessions(sid) := traceRec;

  end;

  -- If the connection is persistent we can show a list of all the sessions
we have turned tracing on for
  procedure showTracedSessions IS
     counter integer;
     strBinds varchar2(20);
  strWaits varchar2(20);
  begin

     counter := 1;
     --  We need to test that there is in fact data otherwise it will crash.
  if sessions.COUNT = 0 then
    return;
  end if;

     for x in sessions.FIRST..sessions.LAST LOOP
     if (sessions.EXISTS(x)) then

     if sessions(x).binds then
        strBinds := 'Binds = TRUE ';
     else
        strBinds := 'Binds = FALSE ';
     end if;

     if sessions(x).waits then
        strWaits := 'Waits = TRUE ';
     else
        strWaits := 'Waits = FALSE ';
     end if;

        dbms_output.put_line('Session ' || rpad(counter,2) || x || ',' ||
sessions(x).serial || '   ' || strBinds || strWaits );
        counter := counter + 1;

     end if;
  end loop;
  end;

  -- Stop a trace
  procedure endTrace(sid BINARY_INTEGER, serial BINARY_INTEGER) IS
  begin
     --stop the trace
  dbms_system.SET_SQL_TRACE_IN_SESSION(sid,serial,false);

  -- remove from internal structures
     if sessions.EXISTS(sid) then
     sessions.DELETE(sid);
  end if;

  dbms_output.put_line('Tracing has been turned off for SID,SERIAL ' || sid
||',' || serial);

  end;

  -- Stops all traces that we have started
  -- Loop through all the session we have traced and now stop them all
  procedure endAllTraces IS
  begin
     for x in sessions.FIRST..sessions.LAST LOOP
     if (sessions.EXISTS(x)) then
           endTrace(x, sessions(x).serial);
     end if;
  end loop;
  end;

END;
/