Fetch session details

Fetch session details

Session details associated with 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) 

/

Active Sessions running from more than 1 hour

set pages 50000 lines 32767

col USERNAME for a10

col MACHINE for a15

col PROGRAM for a40

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,

to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",

ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,

ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL

From gv$session

WHERE STATUS='ACTIVE'

AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60

ORDER BY MINUTES_LOGGED_ON DESC;

SQLs Running from longtime

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';

set pages 50000 lines 32767

col target format a25

col opname format a40

select sid

      ,opname

      ,target

      ,round(sofar/totalwork*100,2)   as percent_done

      ,start_time

      ,last_update_time

      ,time_remaining

from 

       gv$session_longops

/

select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,

START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;

Session details with SPID

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,

MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')

from gv$session where paddr in (select addr from gv$process where spid = '&spid')

/ 

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='&sid';