Track user sessions,process

Track Logon time of DB user and OS user:

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND'; ‎

Track all Session User Details:

select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';

Track Active Session User Details:

SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status= 'ACTIVE' AND UserName IS NOT NULL;

Track Active User Details:

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND';

Report OS Process ID for each session:

SELECT ses.username || '(' || ses.sid || ')' users, acc.owner owner, acc.OBJECT OBJECT, ses.lockwait, prc.spid os_process
FROM v$process prc, v$access acc, v$session ses
WHERE prc.addr = ses.paddr AND ses.sid = acc.sid;

Show Username and SID/SPID with Program Name:


select sid,name,value from v$spparameter where isspecified='TRUE';‎

SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session
WHERE Status= ‘ACTIVE’ AND UserName IS NOT NULL; –to find active session

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program –active users details
FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

Track Current Transaction in Database:

‎‎select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b
where a.saddr = b.ses_addr;‎