Checking Timing details, Client PID of associated oracle SID
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To Find Session Information Details based on SID or SPID or CLIENTPID
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,
s.serial#,
'*'||s.process||'*' Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
Checking Timing details, Client PID of associated oracle SID
undefine spid
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
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 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine
cli_mach,s.sql_hash_value
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid in(&SPID);
Checking Timing Details of SID and event waiting for
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,
to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,
v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;
Checking for active transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);
Checking what is the Last SQL (input multiple sids)
undefine sid
col "Last SQL" for a70
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (&SIDs);
All Active and Inactive connections
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,
s.serial#,
'*'||s.process||'*' Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and s.type != 'BACKGROUND';
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,
s.serial#,
'*'||s.process||'*' Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
s.status='ACTIVE' and
--(s.last_call_et/3600)<1 and="" br="">nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
Active sessions
select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s
where p.addr=s.paddr
order by substr(s.osuser,1,15);
Session details from Session long ops
select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from
v$session_longops where sid=&SID and serial#=&SERIAL
To list the nodes
set head off
set verify off
set echo off
set pages 1500
set linesize 70
prompt
prompt Environment sketch
prompt ==================================
select /*+ CHOOSE*/
'NODE_NAME.................: '||NODE_NAME,
'CREATION_DATE.............: '||CREATION_DATE,
'CREATED_BY ...............: '||CREATED_BY,
'SUPPORT_CP ...............: '||SUPPORT_CP,
'SUPPORT_FORMS ............: '||SUPPORT_FORMS,
'SUPPORT_WEB ..............: '||SUPPORT_WEB,
'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN,
'STATUS ...................: '||STATUS,
'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN,
'SUPPORT_DB ..............: '||SUPPORT_DB
from apps.fnd_nodes;
Session details thru SPID
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '11533')
Checking Timing details, Client PID of associated oracle SID
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To list count of connections from other machines
select count(1),machine from gv$session where inst_id=2 group by machine;
To get total count of sessions and processes
select count(*) from v$session;
select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext thru sqladdress
select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;
To find sqltext for different sql hashvalue
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)
To list long running forms user sessions
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
To list inactive Sessions respective username
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';
To find session id with set of SPIDs
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');
To find Sql Text given SQLHASH & SQLADDR
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;
To find Undo Generated For a given session
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid='&sessionid';
***APPS 11i*****
To Find Forms User Session Details Given ClientProcess id
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,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.process, 1, 8 ) f60webmx,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
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='US'
), 1, 40 ) form
FROM apps.fnd_logins fl,
gv$process vp,
apps.fnd_login_resp_forms rf,
gv$session vs
--fnd_form_tl ft
WHERE fl.end_time IS NULL
AND fl.start_time > sysdate - 31 /* 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.process='&1'
ORDER BY
user_name,
login_start_time,
spid,
pid,
f60webmx,
sid,
serial#;
Checking Timing Details of SID and event waiting for
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi')
LOGON_TIME,
to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from
v$session a,
v$session_wait b where a.sid=&sid and a.sid=b.sid;
Checking for active transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and
s.sid='&sessionid';
SQL> SQL> Enter value for sessionid: 219
old 1: select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where
t.addr=s.taddr and s.sid='&sessionid'
new 1: select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where
t.addr=s.taddr and s.sid='219';
Checking rollback/Undo segment info used by SID
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr, nvl(s.username,'no transaction') us,s.sid, s.osuser os, s.terminal te, rs.rssize,
rs.xacts, rs.rssize/1048576 Rssize
FROM v$lock l, v$session s,v$rollname r , v$rollstat rs
WHERE l.sid = s.sid(+) AND trunc(l.id1/65536) = r.usn AND l.type = 'TX' AND
l.lmode = 6 AND r.usn=rs.usn and s.sid in (&sid_list_comma_sep);
Checking what is the Last SQL
undefine sid
col "Last SQL" for a70
select s.username, s.sid, t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid = '&sid';
Killing inactive sessions for more than 48hrs
set heading off
set feedback off
spool /PENVI/applcsf/prevent/scripts/kill_session.sql
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
where last_call_et > 43200 and username is not null AND STATUS='INACTIVE';
spool off
exit
Session details complete (Input sid)
set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
SID number := 0 ;
inst_id number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_texts varchar(1000) := '';
undo_size varchar (100) := 'N/A';
cursor cur1 is
select a.inst_id,a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logontime,
round((a.last_call_et/60),2) last_call_et,
a.process proc,
b.spid spid,
event event,
state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and a.sid in (75)
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
for m in cur1
loop
DBMS_OUTPUT.ENABLE(25000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('INSTANCE ID....................... : ' || m.inst_id );
DBMS_OUTPUT.PUT_LINE('SID............................... : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........................... : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME.......................... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS............................ : ' || m.status );
DBMS_OUTPUT.PUT_LINE('Client Machine.....................: ' || m.machine );
DBMS_OUTPUT.PUT_LINE('Terminal.......................... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE('Program........................... : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module............................ : ' || m.module );
DBMS_OUTPUT.PUT_LINE('Action............................ : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value.................... : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE('Logon Time........................ : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE('Last Call Et...................... : ' || m.last_call_et||' '||'min' );
DBMS_OUTPUT.PUT_LINE('ClientPID......................... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE('ServerPID......................... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for Event..........: ' || m.event );
DBMS_OUTPUT.PUT_LINE('Session state .....................: ' || m.state);
for rec_undo in (select nvl(t.used_ublk,0)||' '||'Blocks' undo_size from v$session s,v$transaction t where
s.taddr=t.addr(+) and
s.sid=m.sid )
loop
dbms_output.put_line('Undo Generation for sid is.........: ' ||rec_undo.undo_size);
end loop;
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text sql_texts from v$session s,v$sqltext v where
s.sql_hash_value=v.hash_value and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(' '||rec.sql_texts);
end loop;
for n in ( select t.DISK_READS DISK_READS from gv$session s, gv$sqlarea t
where s.sql_hash_value =t.hash_value and s.sid=m.sid)
loop
dbms_output.put_line('Disk reads due to above SQL execution ' || n.DISK_READS);
end loop;
DBMS_OUTPUT.PUT_LINE(' ' );
DBMS_OUTPUT.PUT_LINE(':------------------------------------------------: ' );
DBMS_OUTPUT.PUT_LINE(' ' );
end loop;
end;
Session details complete (Input SPID)
set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
inst_id number := 0 ;
SID number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_texts varchar(1000) := '';
undo_size varchar (100) := 'N/A';
cursor cur1 is
select a.inst_id, a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logontime,
round((a.last_call_et/60),2) last_call_et,
a.process proc,
b.spid spid,
event event,
state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and b.spid in ( '&spid')
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
for m in cur1
loop
DBMS_OUTPUT.ENABLE(25000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('INSTANCE ID....................... : ' || m.inst_id );
DBMS_OUTPUT.PUT_LINE('SID............................... : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........................... : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME.......................... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS............................ : ' || m.status );
DBMS_OUTPUT.PUT_LINE('Client Machine.....................: ' || m.machine );
DBMS_OUTPUT.PUT_LINE('Terminal.......................... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE('Program........................... : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module............................ : ' || m.module );
DBMS_OUTPUT.PUT_LINE('Action............................ : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value.................... : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE('Logon Time........................ : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE('Last Call Et...................... : ' || m.last_call_et||' '||'min' );
DBMS_OUTPUT.PUT_LINE('ClientPID......................... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE('ServerPID......................... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for Event..........: ' ||m.event );
DBMS_OUTPUT.PUT_LINE('Session state .....................: ' ||m.state);
for rec_undo in (select nvl(t.used_ublk,0)||' '||'Blocks' undo_size from v$session s,v$transaction t where
s.taddr=t.addr(+) and
s.sid=m.sid )
loop
dbms_output.put_line('Undo Generation for sid is.........: ' ||rec_undo.undo_size);
end loop;
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text sql_texts from v$session s,v$sqltext v where
s.sql_hash_value=v.hash_value and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(' '||rec.sql_texts);
end loop;
for n in ( select t.DISK_READS DISK_READS from gv$session s, gv$sqlarea t
where s.sql_hash_value =t.hash_value and s.sid=m.sid)
loop
dbms_output.put_line('Disk reads due to above SQL execution ' || n.DISK_READS);
end loop;
DBMS_OUTPUT.PUT_LINE(' ' );
DBMS_OUTPUT.PUT_LINE(':------------------------------------------------: ' );
DBMS_OUTPUT.PUT_LINE(' ' );
end loop;end;
Count of JDBC thin client sessions grouped by status
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select count(s.sid),s.status from gv$session s, gv$process p where p.addr=s.paddr and s.program || s.module like ('%JDBC Thin Client%') group by status;
JDBC Session count
select count(s.sid) from gv$session s where s.program || s.module like ('%JDBC Thin Client%');
Inactive sessions count
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 1800
and username is not null
and module like '%JDBC Thin Client%'
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 43200
and username is not null
and module like '%JDBC Thin Client%'
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
JDBC Sessions count
SELECT username,count(*) sess
FROM v$session
where username is not null
and module like '%JDBC Thin Client%'
group by username
order by sess DESC;
Machine wise count
select MACHINE, PROCESS,COUNT(*)
from V$SESSION
where program like '%JDBC%'
and username = 'APPS'
and process is not null
group by MACHINE, PROCESS
order by MACHINE ;
Inactive sessions count
SELECT count(*),module FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE' group by module;
SELECT username,status,count(*)sesion FROM v$session where username is not null and module
like '%JDBC Thin Client%' group by username ,status;
select count(status) Count, status, machine, program from v$session where program like '%JDBC%'
group by status, machine,program having status = 'INACTIVE' order by 1;
ACTIVE / INACTIVE Sessions
set linesize 132
set pagesize 100
col machine format a15
col OSuser format a12
col program format a30
SQL> select count(*) from v$session;
SQL> select count(*) from v$session where status='INACTIVE';
SQL> select count(*) from v$session where status='ACTIVE';
SQL> select machine, osuser, program, count(*) from v$session
group by machine, osuser, program order by 4 desc;
SQL> select count(status) Count, status, machine, program from v$session
where program like '%JDBC%' group by status, machine, program;
SQL> select count(status) Count, status, machine, module from v$session
where program = 'JDBC Thin Client' group by status, machine, module;
Logon time of JDBC
SQL> SELECT serial#, substr(program,1,20) program, status,
to_char(logon_time,'DD-MON-YY HH24:SS') Login_Time,
to_char(sysdate-last_call_et/86400,'DD-MON-YY HH24:SS') Last_Activity FROM
v$session
WHERE program like 'JDBC%' order by 4;
Session distribution
select to_char(sysdate,'DD/MM HH24:MI') "DATE",inst_id,count(inst_id) total_ses,sum(decode(status,'INACTIVE',1,0) ) inactive_ses from gv$session group by inst_id;
Program grouped by count of user connection
select unique s.program,s.osuser ,count(1) from v$session s, v$process p where s.username
is not null and s.paddr = p.addr and s.status='INACTIVE' group by s.program,s.osuser;
TOTAL Sessions/Inactive Sessions
select to_char(sysdate,'DD/MM HH24:MI') "DATE", inst_id, count(inst_id) total_ses,
sum(decode(status,'INACTIVE',1,0) )inactive_ses from gv$session group by inst_id
Thru Os user
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,
s.serial#,
'*'||s.process||'*' Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr
and s.osuser='&osuser';
Session accessing an object
select b.sql_text,a.sid,a.serial#,b.users_executing, b.rows_processed,a.last_call_et/3600 Hrs from v$sqlarea b,v$session a where b.sql_text like '&object_name' and a.sql_address=b.address;
select b.sql_text,a.sid,a.serial#,b.users_executing, b.rows_processed,a.last_call_et/3600 Hrs from gv$sqlarea b,gv$session a where sql_text like '%DTEA_PA_REPORTING_AGT_HISTO%' and a.sql_address=b.address;
Select
a.session_id, b.sql_text,
count(*)
from
v$active_session_history a,v$sql b
where
a.session_state= 'ON CPU' and
a.SAMPLE_TIME > sysdate - (120/(24*60)) and a.sql_id=b.sql_id and b.sql_text like '%WF_ITEM_ATTRIBUTE_VALUES%'
group by a.session_id,b.sql_text
order by
count(*) desc;
Listing out details of program thru SQLID
Select
a.session_id, b.sql_text,a.program,a.module,a.action
from
v$active_session_history a,v$sql b
where
a.sql_id=b.sql_id and b.sql_id like '%fk9qzystpcazs%';
How to find apps user when you know the o/s pid in 11i for Forms users (f60webmx 100% CPU)
You have to pass the UNIX process id to this script
column "User Name" format a20
column "ClPID" format a8
select
d.user_name "User Name",
b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "ClPID",
to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"
from
fnd_logins a, v$session b, v$process c, fnd_user d
where
b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)
and a.SPID = &PID;
