[code language=”sql”]
set termout off;
set feedback off
set echo off;
column client format a45;
column username format a14;
column name format a14
column tablespace_name format a50
column db format a9
column db_host format a14
set linesize 100
set pagesize 300

select d.name DB,
i.host_name db_host,
s.username,
s.machine client,
count(*) tot
from v\$session s,
v\$database d,
v\$instance i
where s.username != ‘SYS’
group by s.machine,
s.username,
d.name,
i.host_name,
i.status
order by tot,
s.username desc;

exit;

###############################################################################################
#!/bin/ksh
# Session trace
###############################################################################################

echo
echo "Session IDs and what’s being executed "
echo "======================================"

sqlplus -s "/ as sysdba" <<EOF
set pages 66
set lines 150

column username format a15 word_wrapped
column module format a25 word_wrapped
column action format a20 word_wrapped
column client_info format a30 word_wrapped
column service_name format a30 word_wrapped
column client_identifier format a30 word_wrapped
column sql_id format a15 word_wrapped
column event format a50 word_wrapped
column seconds_in_wait format 999,999
select username||'(‘||sid||’,’||serial#||’)’ username,
module,
action,
client_info,
service_name,
client_identifier,
sql_id,
event,
seconds_in_wait
from v\$session
/* where module||action||client_info is not null; */
where status=’ACTIVE’ and username is not null and username like ‘SYS%’ and username not like ‘FOG%’;

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv\$session s
JOIN gv\$process p ON p.addr = s.paddr AND
p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’;

EOF
[/code]