V$PROCESS和V$SESSION,以及使用这两个视图能做什么

使用V$PROCESS，V$SESSION能做什么

查看哪些用户连到了DB上，使用什么Program连接

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.sid, --Session identifier
       s.serial#,--Session serial number. Used to uniquely identify a session's objects.
       p.spid, --Operating system process identifier
       p.pid, --Oracle process identifier
       s.username,--Oracle DB username
       s.osuser,--Operating system client user name
       s.TERMINAL,--Operating system terminal name
       s.program,--Operating system program name
       s.STATUS--Status of the session.ACTIVE,INACTIVE,KILLED,CACHED,SNIPED   
FROM   v$session s,v$process p
WHERE   p.addr = s.paddr
AND s.type != 'BACKGROUND';

Sample Output:

Kill Session

Column SID         FORMAT 9999999 heading "Sess|ID "
COLUMN SERIAL#     FORMAT 9999999  heading "Serial# "
COLUMN OBJECT_NAME FORMAT A17 heading "OBJ NAME or|TRANS_ID" Trunc
COLUMN OSUSER      FORMAT A10 heading "Op Sys|User ID"
COLUMN USERNAME    FORMAT A8
select
B.SID,
C.SERIAL#,
C.USERNAME,
C.OSUSER,
DECODE(B.ID2, 0, A.OBJECT_NAME,'Trans-'||to_char(B.ID1)) OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,'--Waiting--',
               1,'Null',
               2,'Row Share',
               3,'Row Excl',
               4,'Share',
               5,'Sha Row Exc',
               6,'Exclusive',
                 'Other') "Lock Mode",
DECODE(B.REQUEST,0,' ',
                 1,'Null',
                 2,'Row Share',
                 3,'Row Excl',
                 4,'Share',
                 5,'Sha Row Exc',
                 6,'Exclusive',
                   'Other') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID = B.ID1
    and B.SID = C.SID
    and C.USERNAME is not null
    and A.object_name like upper('inv_rcv_std%') 
/

select t2.username,t2.sid,t2.serial#,t2.logon_time 
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid order by t2.logon_time;
--查看锁


--kill session的基本语法是:alter system kill session 'sid,serial#';
--把锁给KILL掉,下边的例子146为sid，21177为serial#
alter system kill session '146,21177';

All active sql

set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
  for x in
   (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) loop
   if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/

Time since last user activity

set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et

已知spid，查看当前正在执行或最近一次执行的语句

select /*+ ordered */ sql_text from v$sqltext sql
where (sql.hash_value, sql.address) in (
       select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address)
       from v$session s where s.paddr = (select addr from v$process p where p.spid = to_number('&pid')))
       order by piece asc;

v$session join v$process

两个视图的关联方式

select x

FROM   v$session s,v$process p
WHERE   p.addr = s.paddr

Difference between V$Process and v$session

Tom said:
a process can have many sessions.
a session may or may not have a process.

I can use connection multi-plexing to have one physical connection to the database with a single dedicated server (process) and have many sessions going.

I can have a session via a shared server whereby unless I'm active -- I don't have a process (many sessions share a process).

So a process is a process and a session is a session.  A session eventually needs a process, is not tied to a single process.  A process can have zero one or more sessions using it.

Source:Bhavani -- Thanks for the question regarding "Difference between V$Process and v$session"

V$PROCESS

This view contains information about the currently active processes.