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.

Column	Datatype	Description
ADDR	RAW(4 | 8)	Address of process state object
PID	NUMBER	Oracle process identifier
SPID	VARCHAR2(12)	Operating system process identifier
USERNAME	VARCHAR2(15)	Operating system process username. Any two-task user coming across the network has "-T" appended to the username.
SERIAL#	NUMBER	Process serial number
TERMINAL	VARCHAR2(30)	Operating system terminal identifier
PROGRAM	VARCHAR2(48)	Program in progress
TRACEID	VARCHAR2(255)	Trace file identifier
BACKGROUND	VARCHAR2(1)	1 for a background process; NULL for a normal process
LATCHWAIT	VARCHAR2(8)	Address of latch the process is waiting for; NULL if none
LATCHSPIN	VARCHAR2(8)	Address of the latch the process is spinning on; NULL if none
PGA_USED_MEM	NUMBER	PGA memory currently used by the process
PGA_ALLOC_MEM	NUMBER	PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)
PGA_FREEABLE_MEM	NUMBER	Allocated PGA memory which can be freed
PGA_MAX_MEM	NUMBER	Maximum PGA memory ever allocated by the process

V$SESSION

This view lists session information for each current session.

Column	Datatype	Description
SADDR	RAW(4 | 8)	Session address
SID	NUMBER	Session identifier
SERIAL#	NUMBER	Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.
AUDSID	NUMBER	Auditing session ID
PADDR	RAW(4 | 8)	Address of the process that owns the session
USER#	NUMBER	Oracle user identifier
USERNAME	VARCHAR2(30)	Oracle username
COMMAND	NUMBER	Command in progress (last statement parsed); for a list of values, seeTable 7-5. These values also appear in theAUDIT_ACTIONS table.
OWNERID	NUMBER	The column contents are invalid if the value is2147483644. Otherwise, this column contains the identifier of the user who owns the migratable session. For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator.
TADDR	VARCHAR2(8)	Address of transaction state object
LOCKWAIT	VARCHAR2(8)	Address of lock waiting for; null if none
STATUS	VARCHAR2(8)	Status of the session: ACTIVE - Session currently executing SQL INACTIVE KILLED - Session marked to be killed CACHED - Session temporarily cached for use by Oracle*XA SNIPED - Session inactive, waiting on the client
SERVER	VARCHAR2(9)	Server type (DEDICATED|SHARED|PSEUDO|NONE)
SCHEMA#	NUMBER	Schema user identifier
SCHEMANAME	VARCHAR2(30)	Schema user name
OSUSER	VARCHAR2(30)	Operating system client user name
PROCESS	VARCHAR2(12)	Operating system client process ID
MACHINE	VARCHAR2(64)	Operating system machine name
TERMINAL	VARCHAR2(30)	Operating system terminal name
PROGRAM	VARCHAR2(48)	Operating system program name
TYPE	VARCHAR2(10)	Session type
SQL_ADDRESS	RAW(4 | 8)	Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
SQL_HASH_VALUE	NUMBER	Used with SQL_ADDRESS to identify the SQL statement that is currently being executed
SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement that is currently being executed
SQL_CHILD_NUMBER	NUMBER	Child number of the SQL statement that is currently being executed
PREV_SQL_ADDR	RAW(4 | 8)	Used with PREV_HASH_VALUE to identify the last SQL statement executed
PREV_HASH_VALUE	NUMBER	Used with SQL_HASH_VALUE to identify the last SQL statement executed
PREV_SQL_ID	VARCHAR2(13)	SQL identifier of the last SQL statement executed
PREV_CHILD_NUMBER	NUMBER	Child number of the last SQL statement executed
MODULE	VARCHAR2(48)	Name of the currently executing module as set by calling theDBMS_APPLICATION_INFO.SET_MODULE procedure
MODULE_HASH	NUMBER	Hash value of the above MODULE
ACTION	VARCHAR2(32)	Name of the currently executing action as set by calling theDBMS_APPLICATION_INFO.SET_ACTION procedure
ACTION_HASH	NUMBER	Hash value of the above action name
CLIENT_INFO	VARCHAR2(64)	Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
FIXED_TABLE_SEQUENCE	NUMBER	This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.
ROW_WAIT_OBJ#	NUMBER	Object ID for the table containing the row specified inROW_WAIT_ROW#
ROW_WAIT_FILE#	NUMBER	Identifier for the datafile containing the row specified inROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not -1.
ROW_WAIT_BLOCK#	NUMBER	Identifier for the block containing the row specified inROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not -1.
ROW_WAIT_ROW#	NUMBER	Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not-1.
LOGON_TIME	DATE	Time of logon
LAST_CALL_ET	NUMBER	If the session STATUS is currentlyACTIVE, then the value represents the elapsed time in seconds since the session has become active. If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.
PDML_ENABLED	VARCHAR2(3)	This column has been replaced by columnPDML_STATUS
FAILOVER_TYPE	VARCHAR2(13)	Indicates whether and to what extent transparent application failover (TAF) is enabled for the session: NONE - Failover is disabled for this session SESSION - Client is able to fail over its session following a disconnect SELECT - Client is able to fail over queries in progress as well See Also: Oracle Database Concepts for more information on TAF Oracle Database Net Services Administrator's Guide for information on configuring TAF
FAILOVER_METHOD	VARCHAR2(10)	Indicates the transparent application failover method for the session: NONE - Failover is disabled for this session BASIC - Client itself reconnects following a disconnect PRECONNECT - Backup instance can support all connections from every instance for which it is backed up
FAILED_OVER	VARCHAR2(3)	Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO)
RESOURCE_CONSUMER_GROUP	VARCHAR2(32)	Name of the session's current resource consumer group
PDML_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL DML enabled mode. IfDISABLED, PARALLEL DML enabled mode is not supported for the session. IfFORCED, the session has been altered to force PARALLEL DML.
PDDL_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL DDL enabled mode. IfDISABLED, PARALLEL DDL enabled mode is not supported for the session. IfFORCED, the session has been altered to force PARALLEL DDL.
PQ_STATUS	VARCHAR2(8)	If ENABLED, the session is in a PARALLEL QUERY enabled mode. IfDISABLED, PARALLEL QUERY enabled mode is not supported for the session. IfFORCED, the session has been altered to force PARALLEL QUERY.
CURRENT_QUEUE_DURATION	NUMBER	If queued (1), the current amount of time the session has been queued. If not currently queued, the value is0.
CLIENT_IDENTIFIER	VARCHAR2(64)	Client identifier of the session
BLOCKING_SESSION_STATUS	VARCHAR2(11)	Blocking session status: VALID NO HOLDER GLOBAL NOT IN WAIT UNKNOWN
BLOCKING_INSTANCE	NUMBER	Instance identifier of blocking session
BLOCKING_SESSION	NUMBER	Session identifier of blocking session
SEQ#	NUMBER	Sequence number that uniquely identifies the wait. Incremented for each wait.
EVENT#	NUMBER	Event number
EVENT	VARCHAR2(64)	Resource or event for which the session is waiting See Also: Appendix C, "Oracle Wait Events"
P1TEXT	VARCHAR2(64)	Description of the first additional parameter
P1	NUMBER	First additional parameter
P1RAW	RAW(4)	First additional parameter
P2TEXT	VARCHAR2(64)	Description of the second additional parameter
P2	NUMBER	Second additional parameter
P2RAW	RAW(4)	Second additional parameter
P3TEXT	VARCHAR2(64)	Description of the third additional parameter
P3	NUMBER	Third additional parameter
P3RAW	RAW(4)	Third additional parameter
WAIT_CLASS_ID	NUMBER	Identifier of the wait class
WAIT_CLASS#	NUMBER	Number of the wait class
WAIT_CLASS	VARCHAR2(64)	Name of the wait class
WAIT_TIME	NUMBER	A nonzero value is the session's last wait time. A zero value means the session is currently waiting.
SECONDS_IN_WAIT	NUMBER	If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. IfWAIT_TIME >0, thenSECONDS_IN_WAIT is the seconds since the start of the last wait, andSECONDS_IN_WAIT -WAIT_TIME /100 is the active seconds since the last wait ended.
STATE	VARCHAR2(19)	Wait state: 0 - WAITING (the session is currently waiting) -2 - WAITED UNKNOWN TIME (duration of last wait is unknown) -1 - WAITED SHORT TIME (last wait <1/100th of a second) >0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
SERVICE_NAME	VARCHAR2(64)	Service name of the session
SQL_TRACE	VARCHAR2(8)	Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)
SQL_TRACE_WAITS	VARCHAR2(5)	Indicates whether wait tracing is enabled (TRUE) or not (FALSE)
SQL_TRACE_BINDS	VARCHAR2(5)	Indicates whether bind tracing is enabled (TRUE) or not (FALSE)

转载请注明出处: http://blog.youkuaiyun.com/pan_tian/article/details/7731843
======EOF======