DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 24, 2012

Queries to get the SESSION INFORMATION



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
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;

3 comments:

Prima Source said...

A very interesting article. The insights are really helpful and informative. Thanks for posting. Outsourcing Global Delivery|IT Offshoring and OutsourcingTop IT Companies In India

Anonymous said...

Hi Doyensys,Its very useful and helpful, really nice info. Hope this may also useful here:
https://ora-data.blogspot.in/2016/11/query-session-details.html

Thanks,

Anonymous said...

Hi Doyensys,Its very useful and helpful, really nice info. Hope this may also useful here:
Session info Query

Thanks,