Count of sessions:
select
count(s.status) TOTAL_SESSIONS
from
gv$session s;
Count of Inactive
sessions:
select
count(s.status) INACTIVE_SESSIONS
from
gv$session s, v$process p
where
p.addr=s.paddr
and
s.status='INACTIVE';
SESSIONS WHICH ARE IN
INACTIVE STATUS FROM MORE THAN Last 1HOUR:
select
count(s.status) "INACTIVE SESSIONS > 1HOUR "
from
gv$session s, v$process p
where
p.addr=s.paddr
and
s.last_call_et
> 3600 and
s.status='INACTIVE';
COUNT OF ACTIVE SESSIONS:
select
count(s.status) ACTIVE_SESSIONS
from
gv$session s, v$process p
where
p.addr=s.paddr
and
s.status='ACTIVE';
TOTAL SESSIONS COUNT ORDERED
BY PROGRAM:
col
program for a30
select
s.program,count(s.program) Total_Sessions
from
gv$session s, v$process p
where p.addr=s.paddr
group
by s.program;
TOTAL COUNT OF SESSIONS
ORDERED BY MODULE:
col
module for a30
prompt
TOTAL SESSIONS
select
s.module,count(s.sid) Total_Sessions
from
gv$session s, v$process p
where p.addr=s.paddr
group
by s.module;
TOTAL COUNT OF SESSIONS
ORDERED BY ACTION:
col
action for a30
prompt
TOTAL SESSIONS
select
s.action,count(s.sid) Total_Sessions
from
gv$session s, v$process p
where p.addr=s.paddr
group
by s.action;
INACTIVE SESSIONS:
prompt
INACTIVE SESSIONS
select
p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program
from
gv$session s, v$process p
where
p.addr=s.paddr
and
s.status='INACTIVE';
INACTIVE:
prompt
INACTIVE SESSIONS
select
count(s.status) INACTIVE
from
gv$session s, gv$sqlarea t,v$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE';
INACTIVE PROGRAMS:
col
module for a40
prompt
INACTIVE SESSIONS
col
INACTIVE_PROGRAMS FOR A40
select
distinct (s.program) INACTIVE_PROGRAMS,s.module
from
gv$session s, v$process p
where p.addr=s.paddr and
s.status='INACTIVE';
INACTIVE PROGRAMS with disk
reads:
prompt
INACTIVE SESSIONS
select
distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)
from
gv$session s, gv$sqlarea t,v$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
GROUP
BY S.PROGRAM;
INACTIVE SESSIONS COUNT WITH
PROGRAM:
col
program for a30
prompt
TOTAL INACTIVE SESSIONS
col
INACTIVE_PROGRAMS FOR A40
select
s.program,count(s.program) Total_Inactive_Sessions
from
gv$session s,v$process p
where
p.addr=s.paddr
AND
s.status='INACTIVE'
group
by s.program
order
by 2 desc;
TOTAL INACTIVE SESSIONS MORE
THAN 1HOUR:
col
program for a30
col
INACTIVE_PROGRAMS FOR A40
select
s.program,count(s.program) Inactive_Sessions_from_1Hour
from
gv$session s,v$process p
where
p.addr=s.paddr
AND
s.status='INACTIVE'
and
s.last_call_et > (3600)
group
by s.program
order
by 2 desc;
TOTAL INACTIVE SESSIONS
GROUP BY MODULE:
col
program for a60
COL
MODULE FOR A30
prompt
TOTAL SESSIONS
col
INACTIVE_PROGRAMS FOR A40
select
s.module,count(s.module) Total_Inactive_Sessions
from
gv$session s,v$process p
where
p.addr=s.paddr
AND
s.status='INACTIVE'
group
by s.module;
INACTIVE SESSION DETAILS
MORE THAN 1 HOUR:
set
pagesize 40
col
INST_ID for 99
col
spid for a10
set
linesize 150
col
PROGRAM for a10
col
action format a10
col logon_time format a16
col module format a13
col
cli_process format a7
col
cli_mach for a15
col
status format a10
col
username format a10
col
last_call_et_Hrs for 9999.99
col
sql_hash_value for 9999999999999col username for a10
set
linesize 152
set
pagesize 80
col
"Last SQL" for a60
col
elapsed_time for 999999999999
select
p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs
,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30)
"Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.last_call_et > (3600)
order
by last_call_et;
INACTIVE PROGRAM :
select
p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs
,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30)
"Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
And
s.program='&PROGRAM_NAME'
order
by last_call_et;
INACTIVE MODULES
select
p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs
,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30)
"Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
And
s.module like '%order_cleanup_hazmat_v3.sql'
order
by last_call_et;
INACTIVE JDBC SESSIONS:
set
pagesize 40
col
INST_ID for 99
col
spid for a10
set
linesize 150
col
PROGRAM for a10
col
action format a10
col logon_time format a16
col module format a13
col
cli_process format a7
col
cli_mach for a15
col
status format a10
col
username format a10
col
last_call_et for 9999.99
col
sql_hash_value for 9999999999999col username for a10
set
linesize 152
set
pagesize 80
col
"Last SQL" for a60
col
elapsed_time for 999999999999
select
p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,
s.module,s.program,t.disk_reads,lpad(t.sql_text,30)
"Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.program='JDBC Thin Client'
and
s.last_call_et > 3600
order
by last_call_et;
COUNT OF INACTIVE SESSIONS
MORE THAN ONE HOUR:
SELECT
COUNT(P.SPID)
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.program='JDBC Thin Client'
and
s.last_call_et > 3600
order
by last_call_et;
TOTAL FORM SESSIONS:
SELECT
COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S
WHERE
S.STATUS='INACTIVE' and
s.action
like ('%FRM%');
FORMS SESSIONS DETAILS:
col
"Last SQL" for a30
select
p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
s.sid,t.disk_reads,
t.elapsed_time,lpad(t.sql_text,30) "Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.action
like ('FRM%') and
s.last_call_et
> 3600
order
by spid;
col
machine for a15
col
"Last SQL" for a30
select
p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
S.ACTION,s.process
Client_Process,s.machine
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.action
like ('FRM%') and
s.last_call_et
> 3600;
order
by 4;
INACTIVE FORMS SESSIONS
DETAILS:
col
program for a15
col
last_call_et for 999.99
select
p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et
,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30)
"Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.action like 'FRM:%'
and
s.last_call_et > 3600
order
by last_call_et desc;
UNIQUE SPID:
select unique(p.spid)
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.action like 'FRM:%'
and
s.last_call_et > 3600;
COUNT FORMS:
select
COUNT(p.spid)
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.action like 'FRM:%'
and
s.last_call_et > 3600;
ZERO HASH VALUE:
select
COUNT(p.spid)
from
gv$session s,gv$process p
where
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.action like 'FRM:%'
and
s.last_call_et > 3600
AND
S.SQL_HASH_VALUE=0;
INACTIVE FORM BY NAME:
select
count(s.sid) from v$session S
where
s.action like ('%&ACTION%')
AND
S.STATUS='INACTIVE';
GROUP BY ACTION:
SELECT
S.ACTION,COUNT(S.SID) FROM V$SESSION S
WHERE
S.STATUS='INACTIVE' and
s.action
like ('%FRM%')
group
by s.action;
FROM A SPECIFIC USERNAME:
SET
LINSIZE 152
col
spid for a10
col
process_spid for a10
col
user_name for a20
col
form_name for a20
select
a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY
HH24:MI:SS') "START_TIME" ,
d.user_form_name
"FORM_NAME"
from
apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,
apps.fnd_form_tl
d
where
a.login_id=b.login_id
and
c.user_name like 'JROMO'
and
a.user_id=c.user_id
and
trunc(b.start_time) >trunc(sysdate -11)
and
trunc(b.end_time) is null
and
b.form_id=d.form_id
and
d.language='US';
INACTIVE FORM:
set
pagesize 40
col
INST_ID for 99
col
spid for a10
set
linesize 150
col
PROGRAM for a10
col
action format a10
col logon_time format a16
col module format a13
col
cli_process format a7
col
cli_mach for a15
col
status format a10
col
username format a10
col
last_call_et for 9999.99
col
sql_hash_value for 9999999999999col username for a10
set
linesize 152
set
pagesize 80
col
"Last SQL" for a30
col
elapsed_time for 999999999999
select
p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30)
"Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='INACTIVE'
and
s.action like ('FRM%')
and
s.last_call_et > (3600*3)
order
by last_call_et;
INACTIVE FORM SESSIONS :
col
cli_proc for a9
COL
AUDSID FOR A6
COL
PID FOR A6
COL
SID FOR A5
COL
FORM_NAME FOR A25
COL
USER_NAME FOR A15
col
last_call_et for 9999.99
SELECT
--
/*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
(
SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM
apps.fnd_user fu
WHERE
fu.user_id = fl.user_id
)
user_name,vs.status,
TO_CHAR
( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR
( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600
last_call_et,
SUBSTR
( fl.process_spid, 1, 6 ) spid,
SUBSTR
( vs.process, 1, 8 ) cli_proc,
SUBSTR
( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR
( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR
( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR
( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR
( vs.module || ' - ' ||
(
SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM
apps.fnd_form_tl ft
WHERE
ft.application_id = rf.form_appl_id
AND
ft.form_id = rf.form_id
AND
ft.language = USERENV('LANG')
),
1, 40 ) form_name
FROM
apps.fnd_logins fl,
gv$process vp,
apps.fnd_login_resp_forms
rf,
gv$session vs
WHERE
fl.start_time > sysdate - 7 /* login
within last 7 days */
AND
fl.login_type = 'FORM'
AND
fl.process_spid = vp.spid
AND
fl.pid = vp.pid
AND
fl.login_id = rf.login_id
AND
rf.end_time IS NULL
AND
rf.audsid = vs.audsid
and
vs.status='INACTIVE'
ORDER
BY
vs.process,
fl.process_spid;
ACTIVE:
prompt
ACTIVE SESSIONS
select
count(s.status) ACTIVE
from
gv$session s, gv$sqlarea t,v$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.status='ACTIVE';
MODULE:
set
pagesize 40
col
INST_ID for 99
col
spid for a10
set
linesize 150
col
PROGRAM for a10
col
action format a10
col logon_time format a16
col module format a13
col
cli_process format a7
col
cli_mach for a15
col
status format a10
col
username format a10
col
last_call_et for 9999.99
col
sql_hash_value for 9999999999999col username for a10
set
linesize 152
set
pagesize 80
col
"Last SQL" for a30
col
elapsed_time for 999999999999
select
p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30)
"Last SQL"
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr
and
s.MODULE like ('&MODULE_NAME_1HR%')
and
s.last_call_et > ('&TIME_HRS' * 3600)
order
by last_call_et;
select
p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from
gv$session s, gv$sqlarea t,gv$process p
where
s.sql_address =t.address and
p.addr=s.paddr
and
s.MODULE like ('%TOAD%')
Order
by last_call_et;
TOAD SESSIONS:
select
p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from
gv$session s, gv$process p
where
p.addr=s.paddr
and
s.MODULE like ('%TOAD%')
Order
by last_call_et;
CLIENT MACHINE SESSIONS
COUNT:
select
count(s.process) TOTAL from v$session S
where
s.machine like ('%&CLIENT_MACHINE%');
select
count(s.process) INACTIVE from v$session S
where
s.machine like ('%&CLIENT_MACHINE%')
and
s.status='INACTIVE';
hash value=0:
select
count(s.process) from v$session S
where
s.machine like ('%&CLIENT_MACHINE%')
AND
S.SQL_HASH_VALUE=0;
select
count(s.process) from v$session S
where
s.machine like ('%&CLIENT_MACHINE%')
AND
S.SQL_HASH_VALUE=0
AND
S.LAST_CALL_ET > 3600;
Unique Actions
col
module for a40
prompt
INACTIVE SESSIONS
col
INACTIVE_PROGRAMS FOR A40
select
distinct (s.program) INACTIVE_PROGRAMS,s.module
from
gv$session s, gv$sqlarea t,v$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
s.machine
like ('%&CLIENT_MACHINE%') AND
p.addr=s.paddr
and
s.status='INACTIVE';
GROUP BY program
col
program for a60
prompt
TOTAL SESSIONS
col
INACTIVE_PROGRAMS FOR A40
select
s.program,count(s.program) Total_Inactive_Sessions
from
gv$session s, gv$sqlarea t,v$process p
where
s.sql_address =t.address and
s.sql_hash_value
=t.hash_value and
p.addr=s.paddr AND
s.machine
like ('%&CLIENT_MACHINE%') AND
s.status='INACTIVE'
group by s.program;
