How do I show running processes in Oracle DB

Question

Is it possible to show other processes in progress on an Oracle database   Something like Sybases sp who

User · Accepted Answer

I suspect you would just want to grab a few columns from V$SESSION and the SQL statement from V$SQL. Assuming you want to exclude the background processes that Oracle itself is running

SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
  FROM v$session sess,
       v$sql     sql
 WHERE sql.sql_id(+) = sess.sql_id
   AND sess.type     = 'USER'

The outer join is to handle those sessions that aren't currently active, assuming you want those. You could also get the sql_fulltext column from V$SQL which will have the full SQL statement rather than the first 1000 characters, but that is a CLOB and so likely a bit more complicated to deal with.

Realistically, you probably want to look at everything that is available in V$SESSION because it's likely that you can get a lot more information than SP_WHO provides.

User · Answer

Keep in mind that there are processes on the database which may not currently support a session   If you re interested in all processes you ll want to look to v process  or gv process on RAC

User · Answer

After looking at sp who  Oracle does not have that ability per se   Oracle has at least 8 processes running which run the db  Like RMON etc   You can ask the DB which queries are running as that just a table query   Look at the V  tables   Quick Example   SELECT sid         opname         sofar         totalwork         units         elapsed seconds         time remaining FROM v session longops WHERE sofar    totalwork

User · Answer

After looking at sp who  Oracle does not have that ability per se   Oracle has at least 8 processes running which run the db  Like RMON etc   You can ask the DB which queries are running as that just a table query   Look at the V  tables   Quick Example   SELECT sid         opname         sofar         totalwork         units         elapsed seconds         time remaining FROM v session longops WHERE sofar    totalwork

User · Answer

Keep in mind that there are processes on the database which may not currently support a session   If you re interested in all processes you ll want to look to v process  or gv process on RAC

User · Answer

After looking at sp who  Oracle does not have that ability per se   Oracle has at least 8 processes running which run the db  Like RMON etc   You can ask the DB which queries are running as that just a table query   Look at the V  tables   Quick Example   SELECT sid         opname         sofar         totalwork         units         elapsed seconds         time remaining FROM v session longops WHERE sofar    totalwork

User · Answer

This one shows SQL that is currently  ACTIVE  -  select S USERNAME  s sid  s osuser  t sql id  sql text from v sqltext with newlines t V SESSION s where t address  s sql address and t hash value   s sql hash value and s status    ACTIVE  and s username  lt  gt   SYSTEM  order by s sid t piece     This shows locks   Sometimes things are going slow  but it s because it is blocked waiting for a lock   select   object name     object type     session id     type          -- Type or system user lock   lmode         -- lock mode in which session holds lock   request     block     ctime         -- Time since current mode was granted from   v locked object  all objects  v lock where   v locked object object id   all objects object id AND   v lock id1   all objects object id AND   v lock sid   v locked object session id order by   session id  ctime desc  object name     This is a good one for finding long operations  e g  full table scans    If it is because of lots of short operations  nothing will show up   COLUMN percent FORMAT 999 99   SELECT sid  to char start time  hh24 mi ss   stime   message   sofar totalwork   100 percent  FROM v session longops WHERE sofar totalwork  lt  1

User · Answer

This one shows SQL that is currently  ACTIVE  -  select S USERNAME  s sid  s osuser  t sql id  sql text from v sqltext with newlines t V SESSION s where t address  s sql address and t hash value   s sql hash value and s status    ACTIVE  and s username  lt  gt   SYSTEM  order by s sid t piece     This shows locks   Sometimes things are going slow  but it s because it is blocked waiting for a lock   select   object name     object type     session id     type          -- Type or system user lock   lmode         -- lock mode in which session holds lock   request     block     ctime         -- Time since current mode was granted from   v locked object  all objects  v lock where   v locked object object id   all objects object id AND   v lock id1   all objects object id AND   v lock sid   v locked object session id order by   session id  ctime desc  object name     This is a good one for finding long operations  e g  full table scans    If it is because of lots of short operations  nothing will show up   COLUMN percent FORMAT 999 99   SELECT sid  to char start time  hh24 mi ss   stime   message   sofar totalwork   100 percent  FROM v session longops WHERE sofar totalwork  lt  1

User · Answer

After looking at sp who  Oracle does not have that ability per se   Oracle has at least 8 processes running which run the db  Like RMON etc   You can ask the DB which queries are running as that just a table query   Look at the V  tables   Quick Example   SELECT sid         opname         sofar         totalwork         units         elapsed seconds         time remaining FROM v session longops WHERE sofar    totalwork

[oracle] How do I show running processes in Oracle DB?

Examples related to oracle

Examples related to process