Oracle 查看进程 SQL 查询

在 Oracle 数据库中，您可以使用以下几种 SQL 查询来查看当前运行的进程和会话信息：

1. 查看所有会话信息

SELECT sid, serial#, username, status, machine, program, module, 
       action, logon_time, last_call_et/60 "Last Active (min)"
FROM v$session
ORDER BY logon_time;

2. 查看当前正在执行的 SQL 语句

SELECT s.sid, s.serial#, s.username, s.status, 
       s.machine, s.program, s.module,
       q.sql_text, q.sql_id, s.last_call_et
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

3. 查看锁定的会话和阻塞关系

SELECT 
    l.session_id "SID",
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.module,
    s.logon_time,
    o.object_name,
    o.object_type,
    l.oracle_username,
    l.os_user_name,
    l.process,
    DECODE(l.locked_mode, 
           0, 'None',
           1, 'Null',
           2, 'Row Share',
           3, 'Row Excl.',
           4, 'Share',
           5, 'S/Row Excl.',
           6, 'Exclusive',
           l.locked_mode) lock_mode,
    s.blocking_session "Blocking SID"
FROM 
    v$locked_object l,
    dba_objects o,
    v$session s
WHERE 
    l.object_id = o.object_id
    AND l.session_id = s.sid
ORDER BY 
    s.logon_time;

4. 查看长时间运行的会话

SELECT s.sid, s.serial#, s.username, s.status, 
       s.machine, s.program, s.module,
       s.last_call_et/60 "Inactive (min)",
       s.logon_time
FROM v$session s
WHERE s.status = 'INACTIVE'
AND s.last_call_et > 1800  -- 30分钟
ORDER BY s.last_call_et DESC;

5. 查看资源消耗高的会话

SELECT s.sid, s.serial#, s.username, s.status,
       s.machine, s.program, s.module,
       se.value/100 "CPU Usage (sec)",
       st.value "Elapsed Time (sec)"
FROM v$session s, v$sesstat se, v$statname sn,
     v$sesstat st, v$statname tn
WHERE s.sid = se.sid
AND se.statistic# = sn.statistic#
AND sn.name = 'CPU used by this session'
AND s.sid = st.sid
AND st.statistic# = tn.statistic#
AND tn.name = 'session elapsed time'
ORDER BY se.value DESC;

6. 查看后台进程

SELECT program, status, pid, spid
FROM v$session
WHERE type = 'BACKGROUND';

7. 查看操作系统进程与Oracle会话的关联

SELECT s.sid, s.serial#, s.username, s.status,
       s.machine, s.program, s.module,
       p.spid "OS Process ID"
FROM v$session s, v$process p
WHERE s.paddr = p.addr
ORDER BY s.logon_time;

您可以根据需要选择适当的查询来监控Oracle数据库中的进程和会话活动。