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

Answer 1

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;

Answer 2

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.

Answer 3

Under Unix, you can use the ps command to display physical processes that are running after you start an Oracle instance. Assuming that your database name is "mydb", use this command:

ps -aef | grep mydb

Under Windows, Oracle runs with only one process (oracle.exe under Task Manager). Within this process, there are many different threads that carry out the Oracle functions in the background. You can use a tool like PsList to view the threads. Download that tool, open a command prompt to the folder where you saved the file and type:

pslist -d oracle

You will see a a list of the threads that make up the oracle.exe process. Unfortunately it doesn't list each thread's name, but you do get info like the Thread ID, Priority, and so forth.