使用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"