Search This Blog

Total Pageviews

Friday, 27 May 2011

Oracle acvite session info

Oracle session os info ...

Oracle session os info
Oracle session Info 

set linesize 300 pagesize 200 
col kill          for a15
col username      for a12
col machine       for a15
col time          for a20
col "os user"     for a7
col sid_serial    for a12
col "process id"  for a10
col program       for a35
col module like   program
select 
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''    kill ,
substr(a.username,1,15)         "username",
a.osuser                        "os user",
d.spid                          "process id",
a.lockwait                      "lockwait",
a.status                        "status",
trunc(b.value/1024)             "pga (kb)",
trunc(e.value/1024)             "uga (kb)",
a.module                        "module",
substr(a.machine,1,15)          "machine",
a.program                       "program",
substr(to_char(a.logon_time,'dd-mon-yyyy hh24:mi:ss'),1,20) "time",
a.sql_id
from gv$session a,gv$sesstat b,v$statname c,gv$process d,gv$sesstat e,v$statname f
WHERE 1=1
and b.statistic# = c.statistic#
and c.name = 'session pga memory'
and e.statistic# = f.statistic#
and f.name = 'session uga memory'
and a.inst_id=d.inst_id
and e.inst_id=b.inst_id
and e.inst_id=a.inst_id
and b.inst_id=a.inst_id
and b.sid=a.sid
and e.sid=a.sid
and d.addr = a.paddr 
--and a.type   ='user'
and a.username is not null 
order by 6;

with Event ...


set linesize 300 pagesize 200 
col kill          for a15
col username      for a12
col machine       for a15
col time          for a20
col "os user"     for a7
col sid_serial    for a12
col "process id"  for a10
col program       for a35
col module like   program
col EVENT         for a30
select 
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''    kill ,
substr(a.username,1,15)            "username",
a.osuser                           "os user",
d.spid                             "process id",
a.lockwait                         "lockwait",
a.status                           "status",
trunc(b.value/1024)                "pga (kb)",
trunc(e.value/1024)                "uga (kb)",
-- a.module                        "module",
substr(a.machine,1,15)             "machine",
--a.program                        "program",
a.event ,
substr(to_char(a.logon_time,'dd-mon-yyyy hh24:mi:ss'),1,20) "time",
a.sql_id,
a.PREV_SQL_ID
from gv$session a,gv$sesstat b,v$statname c,gv$process d,gv$sesstat e,v$statname f
WHERE 1=1
and b.statistic# = c.statistic#
and c.name = 'session pga memory'
and e.statistic# = f.statistic#
and f.name = 'session uga memory'
and a.inst_id=d.inst_id
and e.inst_id=b.inst_id
and e.inst_id=a.inst_id
and b.inst_id=a.inst_id
and b.sid=a.sid
and e.sid=a.sid
and d.addr = a.paddr 
-- and a.type   ='user'
and a.username is not null 
order by 6;


KILL            username     os user process id lockwait         status     pga (kb)   uga (kb) machine         EVENT                          time                 SQL_ID        PREV_SQL_ID
--------------- ------------ ------- ---------- ---------------- -------- ---------- ---------- --------------- ------------------------------ -------------------- ------------- -------------
'53,10510,@1'   SYS          oracle  6531       00000000896710E8 ACTIVE         2044        684 oraasm12c.local enq: TX - row lock contention  16-apr-2017 11:49:57 f602ubc924gxr 9p6bq1v54k13j
'81,6910,@1'    SYS          oracle  9257                        ACTIVE         4732       1969 oraasm12c.local SQL*Net message to client      16-apr-2017 12:02:42 3qkbfxh1ncz2b 3qkbfxh1ncz2b
'57,59141,@1'   SYS          oracle  9110                        INACTIVE        956        276 oraasm12c.local SQL*Net message from client    16-apr-2017 12:02:08               f602ubc924gxr





No comments:

Oracle DBA

anuj blog Archive