Oracle Forms Session Queries
Oracle Forms Session Queries
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 s.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;