Oracle v$session v$mystat v$process

Oracle中有时需要查询数据库连接信息与其相关的服务器进程信息.

--所有数据库会话
select sid,serial#,username,program,machine,status from v$session;

--当前数据库连接会话
select SID,SERIAL#,PADDR,USERNAME,PROGRAM from v$session where sid=(select distinct sid from v$mystat);

--所有OS进程ps -ef的显示结果
select pid,spid,username,serial#,program from v$process;

--当前会话的OS进程号
select spid from v$process where addr=(select PADDR from v$session where sid=(select distinct sid from v$mystat));

--根据用户查询OS进程号
select pid,spid from v$process where addr in (select paddr from v$session where username in ('HR','SH'));

查询被锁的表
v$process,v$session,v$locked_object,all_objects

select p.spid,
       a.serial#,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name
from   v$process p,v$session a, v$locked_object b,all_objects c
where  p.addr=a.paddr and
       a.process=b.process and
       c.object_id=b.object_id