set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 130
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name          : listSessionsInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This utility will display all connected sessions to the
PROMPT                     database or just for the specified user
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named sessionsInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column os_user     format a10 heading "OS User" wrap
column username    format a10 heading "Oracle|User Name"  wrap
column box         format a10 heading "Machine|Name"      wrap
column sid_serial  format a15 heading "(SID, Serial#)" 
column value                  heading "CPU Used"
column logon_time  format a20 heading "Login Time" 
column command     format a24 heading "Command"           word_wrap
column program     format a20 heading "Program"           word_wrap
column login_info  format a80 heading "Login Information" word_wrap

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
-- allow user name to be entered.  NULL/return key is for all locks/users
accept u_name prompt 'Enter the name of Oracle user to show sessions for (return for all users) : ' 

--set termout off
set feedback off

set head on

spool sessionsInfo_&s_dbname._&s_machine

select
   rpad(c.name||':',11)||rpad(' current logons='||
   (to_number(b.sessions_current)),20)||'cumulative logons='||
   rpad(substr(a.value,1,10),10)||'highwater mark='||
   b.sessions_highwater login_info
from
   v$sysstat a, 
   v$license b, 
   v$database c 
where a.name = 'logons cumulative';

ttitle left &s_curDate -
       center "List of Database Sessions in Instance "  &s_dbname " on " &s_machine skip 2

break on os_user skip 1

select
       substr(a.spid,1,9) pid,
       b.sid sid,
       substr(b.serial#,1,5) ser#,
       substr(b.username,1,20) username,
       substr(b.machine,1,20) box,
       substr(b.osuser,1,20) os_user,
       substr(b.program,1,20) program
from v$session b,
     v$process a
where b.paddr = a.addr
  and type='USER'
and nvl(b.username,1) like upper(nvl('&&u_name','%'))
order by os_user;

ttitle left &s_curDate -
       center "Status of Database Sessions in Instance "  &s_dbname  " on " &s_machine skip 2

break on os_user skip 1

select
       b.sid sid,
       substr(b.serial#,1,5) ser#,
       substr(b.username,1,20) username,
       substr(b.machine,1,20) box,
       substr(b.program,1,20) program,
       decode(b.command, 
                0,'BACKGROUND', 
                1,'Create Table', 
                2,'INSERT', 
                3,'SELECT', 
                4,'CREATE CLUSTER', 
                5,'ALTER CLUSTER', 
                6,'UPDATE', 
                7,'DELETE', 
                8,'DROP', 
                9,'CREATE INDEX', 
                10,'DROP INDEX', 
                11,'ALTER INDEX', 
                12,'DROP TABLE', 
                13,'CREATE SEQUENCE', 
                14,'ALTER SEQUENCE', 
                15,'ALTER TABLE', 
                16,'DROP SEQUENCE', 
                17,'GRANT', 
                18,'REVOKE', 
                19,'CREATE SYNONYM', 
                20,'DROP SYNONYM', 
                21,'CREATE VIEW', 
                22,'DROP VIEW', 
                23,'VALIDATE INDEX', 
                24,'CREATE PROCEDURE', 
                25,'ALTER PROCEDURE', 
                26,'LOCK TABLE', 
                27,'NO OPERATION', 
                28,'RENAME', 
                29,'COMMENT', 
                30,'AUDIT', 
                31,'NOAUDIT', 
                32,'CREATE EXTERNAL DATABASE', 
                33,'DROP EXTERNAL DATABASE', 
                34,'CREATE DATABASE', 
                35,'ALTER DATABASE', 
                36,'CREATE ROLLBACK SEGMENT', 
                37,'ALTER ROLLBACK SEGMENT', 
                38,'DROP ROLLBACK SEGMENT', 
                39,'CREATE TABLESPACE', 
                40,'ALTER TABLESPACE', 
                41,'DROP TABLESPACE', 
                42,'ALTER SESSION', 
                43,'ALTER USER', 
                44,'COMMIT', 
                45,'ROLLBACK', 
                46,'SAVEPOINT', 
                47,'PL/SQL EXECUTE', 
                48,'SET TRANSACTION', 
                49,'ALTER SYSTEM SWITCH LOG', 
                50,'EXPLAIN', 
                51,'CREATE USER', 
                52,'CREATE ROLE', 
                53,'DROP USER', 
                54,'DROP ROLE', 
                55,'SET ROLE', 
                56,'CREATE SCHEMA', 
                57,'CREATE CONTROL FILE', 
                58,'ALTER TRACING', 
                59,'CREATE TRIGGER', 
                60,'ALTER TRIGGER', 
                61,'DROP TRIGGER', 
                62,'ANALYZE TABLE', 
                63,'ANALYZE INDEX', 
                64,'ANALYZE CLUSTER', 
                65,'CREATE PROFILE', 
                66,'DROP PROFILE', 
                67,'ALTER PROFILE', 
                68,'DROP PROCEDURE', 
                69,'DROP PROCEDURE',
                70,'ALTER RESOURCE COST', 
                71,'CREATE SNAPSHOT LOG', 
                72,'ALTER SNAPSHOT LOG', 
                73,'DROP SNAPSHOT LOG', 
                74,'CREATE SNAPSHOT', 
                75,'ALTER SNAPSHOT', 
                76,'DROP SNAPSHOT', 
                79,'ALTER ROLE',
                85,'TRUNCATE TABLE', 
                86,'TRUNCATE CLUSTER', 
                87,'-', 
                88,'ALTER VIEW', 
                89,'-', 
                90,'-', 
                91,'CREATE FUNCTION', 
                92,'ALTER FUNCTION', 
                93,'DROP FUNCTION', 
                94,'CREATE PACKAGE', 
                95,'ALTER PACKAGE', 
                96,'DROP PACKAGE', 
                97,'CREATE PACKAGE BODY', 
                98,'ALTER PACKAGE BODY', 
                99,'DROP PACKAGE BODY', 
                b.command ||' - UNKNOWN') || 
		decode(tx.sql_text,NULL,NULL,' - ' || tx.sql_text) command, 
	b.status, to_char(b.logon_time,'MM-DD-YYYY HH24:MI:SS') logon_time
from v$session b,
     v$process a,
     v$sqlarea tx
where b.paddr = a.addr
  and b.sql_hash_value = tx.hash_value (+)
  and type='USER'
  and nvl(b.username,1) like upper(nvl('&&u_name','%'))
order by status;

ttitle left &s_curDate -
       center "CPU Usage of Database Sessions in Instance "  &s_dbname " on " &s_machine skip 2

select nvl(ss.username,'BACKGROUND') username, 
       ' ('||ss.sid||','||ss.serial#||') ' sid_serial,
       se.value
from v$session ss, 
     v$sesstat se,
     v$statname sn
where se.statistic# = sn.statistic#
  and sn.name like '%CPU used by this session%'
  and se.sid = ss.sid
and nvl(ss.username,1) like upper(nvl('&&u_name','%'))
order by value desc, substr(name,1,25);

ttitle off
set heading off
set feedback off

select 'To kill, enter SQLPLUS>  ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no session information was found for the database &s_dbname
PROMPT --                or no session information was found for the username &u_name
PROMPT
PROMPT
PROMPT Output file sessionsInfo_&s_dbname._&s_machine..LST produced
PROMPT

undefine u_name

@sqlplus_settings