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.
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;
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;
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 <> '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 < 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;
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)
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;
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)
Source: Stackoverflow.com