Script to Gather data from the database for Process information and User connections

REM
REM START OF SQL
REM
REM  Filename     : mzRunSQL.sql
REM  Author       : Mike Shaw
REM  Date Updated : 29 August 2008
REM  Purpose      : Script to gather data from the database for process
REM                 information and user connections
REM
set serveroutput on size 1000000
set echo on
set timing on
set feedback on
set long 10000
set pagesize 132
set linesize 110
col username form a10
col program form a30
col how_many forma 9999
col machine form a25
col module form a50
--
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') START_TIME from dual
/
rem Instance identification
select *
from v$instance
/
REM Summary of database connections
select s.module, s.machine, s.username, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by rollup(s.module,s.machine,s.username)
/
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM Get count of number of connected Apps 11i users
REM
col user_name format a15
col first_connect format a18
col last_connect format a18
col How_many_user_sessions format 9999999999
col How_many_sessions format 9999999999
REM
REM Summary of how many users
REM
select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate  
and counter < limit_connects
/
REM
REM Number of sessions per user
REM
select user_name, count(*) How_many_sessions
from icx_sessions icx, fnd_user u
where icx.user_id = u.user_id
and disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate  
and counter < limit_connects
group by user_name
order by 2 desc
/
REM
REM Quick look for blockers and lockers
REM
col BLOCKER form a5
col Username format A15
col Sid format 9990 heading SID
col Type format A4
col Lmode format 990 heading 'HELD'
col Request format 990 heading 'REQ'
col Id1 format 999999999999
col Id2 format 999999999999
break on Id1 skip 1 dup
--
SELECT M.Sid,
M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1,
M.Id2,
DECODE(block, 0, 'NO', 'YES' ) BLOCKER
FROM V$LOCK M
WHERE
M.Request ! = 0 or (M.Request = 0 and Lmode != 4 and (id1, id2) in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1 = M.Id1 and S.Id2 = M.Id2) )
ORDER BY Id1, Id2, M.Request
/
REM
REM Check for database statement leaks
REM May give symptoms like OutOfMemoryError
REM
set lines 132
set pages 1000
column module format A40
column program format A30
select oc.sid,oc.hash_value,oc.sql_text,count(*) How_Many
from gv$open_cursor oc
group by sid,hash_value,sql_text
having count(*) > 5
order by 4
/
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') END_TIME from dual
/
exit;
REM
REM END OF SQL
REM