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)
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
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.
You can shutdown and restart the database or use the ORAKILL utility to kill
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.
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
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
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.
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 死进程,要清理的
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20542911/viewspace-615742/，如需转载，请注明出处，否则将追究法律责任。