如何在 Oracle DB 中显示正在运行的进程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/199508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I show running processes in Oracle DB?
提问by Robert Brown
Is it possible to show other processes in progress on an Oracle database? Something like Sybases sp_who
是否可以显示 Oracle 数据库上正在进行的其他进程？类似于 Sybasessp_who
回答by Justin Cave
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
我怀疑您只是想从 V$SESSION 中获取几列，并从 V$SQL 中获取 SQL 语句。假设您要排除 Oracle 本身正在运行的后台进程
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.
外连接是处理那些当前不活跃的会话，假设你想要这些会话。您还可以从 V$SQL 获取 sql_fulltext 列，该列将包含完整的 SQL 语句而不是前 1000 个字符，但这是一个 CLOB，因此处理起来可能会更复杂一些。
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.
实际上，您可能希望查看 V$SESSION 中可用的所有内容，因为您可以获得比 SP_WHO 提供的信息多得多的信息。
回答by jim
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.
查看 sp_who 后，Oracle 本身没有这种能力。Oracle 至少有 8 个运行数据库的进程。像 RMON 等。
You can ask the DB which queries are running as that just a table query. Look at the V$ tables.
您可以询问 DB 哪些查询正在运行，因为它只是一个表查询。查看 V$ 表。
Quick Example:
快速示例：
SELECT sid,
opname,
sofar,
totalwork,
units,
elapsed_seconds,
time_remaining
FROM v$session_longops
WHERE sofar != totalwork;
回答by WW.
This one shows SQL that is currently "ACTIVE":-
这显示了当前“活动”的 SQL：-
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
/
回答by Ryan
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)
如果您对所有进程感兴趣，您将需要查看 v$process（或 RAC 上的 gv$process）
