REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	c_ses.sql
REM
REM <SQLDIR_GRP>USER TRACE MAINT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports all sessions [ PID PROCESS IDLE MACHINE OSUSER ]
REM     allows tracing optionally calling 
REM		SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_ses.sql
REM 
REM Example:
REM	c_ses.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set wrap off
set pagesize 9999
set linesize 132

col sid format 9999
col serial# format 9999999
col spid format 9999999
column username format a10
column schemaname format a10
column osuser format a10
column machine format a15 trunc
column terminal format a13
column program format a10
column logon format a15
column idle format a8
col maction format a25 trunc

select s.sid ,s.serial#, p.spid
,       s.username
,       to_char(logon_time,'DDth - HH24:MI:SS') logon
,       floor(last_call_et/3600)||':'||
           floor(mod(last_call_et,3600)/60)||':'||
           mod(mod(last_call_et,3600),60) IDLE
,       s.status,s.osuser,s.process,s.machine
,       s.module||' '||s.action maction
from v$session s
,    v$process p
where 'x'='x'
--   (s.process='pid' OR
--       s.paddr = (select addr from v$process where spid='pid'))
and   s.paddr = p.addr
order by sid

/

--
-- turn tracing ON or OFF for a given session
--
ttitle off

prompt
prompt +---------------------------------------------------------+
prompt |    The Following Portion Will Turn tracing ON or OFF    |
prompt |             To exit early press CTL-C                   |
prompt +---------------------------------------------------------+
prompt
accept _sid     number prompt "............................... Enter user SID :"
accept _serial  number prompt "............................Enter user SERIAL# :"
accept _TRUE    char   prompt "Enter TRUE to enable trace or FALSE to disable :"

execute sys.dbms_system.set_sql_trace_in_session(&_sid,&_serial,&_TRUE);