转载:
原文地址: http://blog.itpub.net/20542911/viewspace-615742/
eygle的blog中解释如下:
1.1.1.2 V$PROCESS视图
通过数据库中的v$process视图,可以找到对应于操作系统的每个进程信息:
SQL> select addr,pid,spid,username,program from v$process;
ADDR PID SPID USERNAME PROGRAM
-------- ---------- ---------- -------- ----------------------------------------
5FE162AC 1 PSEUDO
5FE16860 2 6290 oracle oracle@eygle (PMON)
5FE16E14 3 6292 oracle oracle@eygle (PSP0)
5FE173C8 4 6294 oracle oracle@eygle (MMAN)
5FE1797C 5 6296 oracle oracle@eygle (DBW0)
5FE17F30 6 6298 oracle oracle@eygle (LGWR)
5FE184E4 7 6300 oracle oracle@eygle (CKPT)
。。。。。。。。。。。。
注意以上输出,pid=1的进程是一个PSEUDO进程,这个进程被认为是初始化数据库的进程,启动其他进程之前即被占用,并在数据库中一直存在。
为何kill掉session后,server列变成pseudo?
现象
When an inactive session is killed via the alter system kill session command,
the process continues in v$session as KILLED and program as PSEUDO.
Select username,sid,serial#,status from v$session;
USERNAME SID SERIAL# STATUS
------------------------------ ---------- ---------- --------
SYSTEM7 1964 ACTIVE
SCOTT 10 1087 INACTIVE
KARI 11 106 INACTIVE
--> alter system kill session '11,106';
USERNA SPID PROCESS STATUS PROGRAM PROGRAM
------ --------- --------- -------- --------------- ---------------
KARI 299:232 KILLED D:\WINNT\Profil PSEUDO
es\All Users\St
art Menu\Programs\
原理
When an inactive session has been terminated, STATUS in the view V$SESSION is
"KILLED." ,but its row in the v$session view is not removed until you try to
use that session again and "ORA-00028 your session has been killed" is reported,
then it is removed. Until that point, it is marked as killed in the status column
and pseudo in the server column.
If an active session cannot be interrupted (for example, it is performing
network I/O or rolling back a transaction), the session cannot be terminated
until the operation completes. In this case, the session holds all resources
until it is terminated. Additionally, the session that issues the ALTER SYSTEM
statement to terminate a session waits up to 60 seconds for the session to
be terminated; if the operation that cannot be interrupted continues past
one minute, the issuer of the ALTER SYSTEM statement receives a message
indicating that the session has been "marked" to be terminated. A session
marked to be terminated is indicated in V$SESSION with a status of "KILLED"
and a server that is something other than PSEUDO i.e. dedicated,shared, is a active sessions performing a network I/O or rolling back a transaction.
NOTE: Killing the pseudo process (seen at the O/S) can lead to an orphan process
process in Oracle as well. We recommned killing the Oracle process (found in
V$PROCESS) first and then kill at the O/S if necessary.
杀掉session
You can shutdown and restart the database or use the ORAKILL utility to kill
threads.
Oracle has provided an ORAKILL utility that will kill shadow threads. Each
user's connection is represented by a thread in the Oracle process. If a
user's session is killed, then their Oracle session is killed - not the thread.
Oracle has provided an ORAKILL utility which can be passed a thread ID and will
kill the specified thread.
To make sure you do not kill a background process (which would crash your
database), you must perform. a select to ensure you get the correct thread.
---
select p.spid "OS Thread", b.name "Name-User", s.osuser, s.program
from v$process p, v$session s, v$bgprocess b
where p.addr = s.paddr
and p.addr = b.paddr UNION ALL
select p.spid "OS Thread", s.username "Name-User", s.osuser, s.program
from v$process p, v$session s
where p.addr = s.paddr
and s.username is not null;
---
This will list all Shadow processes and backgound processes.
Each shadow process will show the thread ID - this is what must be killed via
the ORAKILL utility.
PMON进程
what happens to a session while it is in the KILLED PSEUDO state?
What's happening is that PMON periodically checks to see if any sessions have been killed. If it finds one, it attempts to rollback the transaction for that session
(that was in progress when it was killed). The reason this can take a long time is
because PMON may have more than one transaction to rollback at a time
(if other sessions have been killed, or if processes have died etc).
Thus, it may take a while to finally cleanup the killed session and have it
disappear from the session monitor. The system i/o monitor correctly shows
the reads and writes being performed by PMON in order to rollback the
session's transaction.
PMON will not delete the session object itself until the client connected to
that session notices that it has been killed. Therefore, the sequence of
events is:
1) alter system kill session is issued - the STATUS of the session object in
V$SESSION becomes KILLED, its server becomes PSEUDO.
2) PMON cleans up the *resources* allocated to the session
(i.e., rolls back its transaction, releases its locks, etc).
3) the entry in V$SESSION remains there until the client of that session (the
client is the process associated with the OSUSER,MACHINE,PROCESS columns in
the V$SESSION view) tries to do another request.
4) the client attempts another SQL statement and gets back ORA-28.
5) PMON can now remove the entry from V$SESSION.
This behavior. is necessary because the client still has pointers to the
session object even though the session has been killed. Therefore, the
object cannot be deleted until the client is no longer pointing at it.
你可以DUMP所有进程
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';
查看下面信息
Process Group: DEFAULT, pseudo proc: c000000109eefda0 O/S info: user: ora9i, term: pts/th, ospid: 22580 ----------------该进程的操作系统进程号,对应于V$PROCESS中的SPID
查看进程状态
SO: 6EDDD3BC, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=14, calls cur/top: 6EE67000/6EE66574, flag: (0)
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 4
last post received-location: kslpsr
last process to post me: 6eddae3c 1 6
last post sent: 0 0 15
last post sent-location: ksasnd
last process posted by me: 6eddae3c 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 6EDFD1D0
O/S info: user: lifeng.fang, term: IT32, ospid: 2828
OSD pid info: Windows thread id: 2828, image: ORACLE.EXE
flag 0x01 死进程,要清理的
0x02 系统进程
0x04 PMON
0X08 SMON
0x10 共享服务器假冒进程
0x20 共享服务器进程
0x40 调度进程