Check how long particular session has been running

[hok1man]

Hi Guys,

Does anyone knows how to check as per subject above?
this is my query, but it doesn't show how long the process has been running :

select 
       substr(a.spid,1,9) pid, 
       substr(b.sid,1,5) sid, 
       substr(b.serial#,1,5) ser#, 
       substr(b.machine,1,6) box, 
       substr(b.username,1,10) username, 
       substr(b.osuser,1,8) os_user, 
       substr(b.program,1,30) program 
from v$session b, v$process a 
where 
b.paddr = a.addr 
and type='USER' 
order by spid;

Also, how to setup the display of the result query looks better readable like a proper table...

such as

PID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM                       

--------- ----- ----- ------ ---------- -------- ------------------------------

29883     66    7     corp-h APPS       applmgr

here's what i tried but doesnt work :
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

Thanks guys,

 

[hoinz]

1) If it's about long running sessions, then a look at v$session_longops may help.

2) You may reduce the display length of columns like this:
column pid format 99999
column username format a10

 

[SantaMufasa]

Hok,

I use the data dictionary view, "gv_session" to gather the information I use to monitor logins, including the LOGON_TIME of each session. You can subract LOGON_TIME from SYSDATE to determine the length of sessions since they started:

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a15 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a21
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select     username oruser
   ,osuser osuser
   ,machine computer
   ,''''||s.sid||','||s.serial#||'''' ss
   ,status
   ,process
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
   ,program
from       gv$session s
order by time
/
ttitle off

BTW, I display, in single quotes, "SID/Serial #" for each session to make it easy to kill unwanted sessions using the code:

ALTER SYSTEM KILL SESSION 'x,y';

...where 'x,y' are the SID/Serial # displayed from the query, above.

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hok1man]

Mufasa,

great job!
one more question, what these codes means?

''''||s.sid||','||s.serial#||''''

I know || means append

can you explain them in together?

thanks,

 

[SantaMufasa]

Yes...Hok,

Recall that Oracle literals appear withing single quotes. If we want to produce a single, literal quote, we must place two successive single quotes inside of single quotes. Thus, ''''.

I concatenated a literal single quote on both the front and the back of a concatenation of the session identifier (s.sid) and the session serial number (s.serial#), separated by a literal comma.

The reason for all of that is, as I mentioned, above:

I display, in single quotes, "SID/Serial #" for each session to make it easy to kill unwanted sessions using the code:

ALTER SYSTEM KILL SESSION 'x,y';

...where 'x,y' are the SID/Serial # displayed from the query, above.

I hope this helps explain things satisfactorily. Let me know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]