关于session 在killed状态下的疑问？
oracle: 10.2.0.4OS:OEL5.5
alter system kill session(106,10172);
alter system kill session(105,3633);
select ses.username,
ses.OSUSER,
ses.sid,
pro.spid,
ses.serial#,
ses.lockwait,
ses.status,
ses.machine,
ses.program,
'kill -9 ' || pro.spid as "OPSYS_Command"
from v$session ses
left join v$process pro
on pro.addr = ses.paddr
where ses.status = 'KILLED'
and ses.OSUSER = 'tl'
/*and pro.spid is not null*/
order by ses.machine;
USERNAME OSUSER SID SPID SERIAL# LOCKWAIT STATUS MACHINE PROGRAM OPSYS_Command
---------- ------------ ------ ---------- ---------- -------- ------------- -------------- -------------
CALLCENTER tl 106 10172 KILLED YC\ENCH-TL plsqldev.exe kill -9
CALLCENTER tl 105 3633 KILLED YC\ENCH-TL plsqldev.exe kill -9
问题1：请问当一个session 用alter system kill session这句话kill以后，数据库都做了些什么？
问题2：上面的查询看不到SPID？ ODM FINDING:
ALTER SYSTEM KILL SESSION
-------------------------
This document describes the behavior of the ALTER SYSTEM KILL SESSION
command new in version 7 of the RDBMS. This command allows the DBA to
terminate a user's Oracle session as rapidly as possible by interrupting
any work it is doing and freeing all of it's resources. This may be useful
when the session has been holding resources that other users need.
I. SYNTAX:
ALTER SYSTEM KILL SESSION 'i,j';
where:
i = the index of the session to kill
j = the serial number of the session to kill
Both of these values can be found in the v$session view which lists
them for each session currently logged on to the database. Both i & j
are required in the command to uniquely specify the session to be deleted.
The session address (SADDR column of v$session) is insufficient for this
purpose because it may be reused at any time for newly allocated sessions.
II. USER DOCUMENTATION
To use the ALTER SYSTEM KILL SESSION command, it is necessary to be
logged on as a user with DBA privileges. Then, the index and serial number
of the session which is to be killed must be queried from the database.
There are two different behaviors that may be exhibited by the KILL SESSION
command and they depend on whether the session to be killed is currently
IDLE or ACTIVE.
A. Killing Idle (Inactive) Sessions
As an example, suppose that user SCOTT is logged on to the database
and a DBA executes the following query:
SQLDBA> select sid,serial#,username,status,server from v$session;
SID SERIAL# USERNAME STATUS SERVER
---------- ---------- ------------------------------ -------- ---------
1 1 ACTIVE DEDICATED
2 1 ACTIVE DEDICATED
3 1 ACTIVE DEDICATED
4 1 ACTIVE DEDICATED
5 1 ACTIVE DEDICATED
7 11 SCOTT INACTIVE DEDICATED
9 11 SYS ACTIVE DEDICATED
7 rows selected.
SQLDBA>
From this result, we can see that SCOTT's session has an index number
of 7 and a serial number of 11. His session is currently inactive, as
indicated by the STATUS column; this means he is not currently calling
the database to execute SQL.
Now, let's issue the kill command and see what happens --
SQLDBA> alter system kill session '7,11';
Statement processed.
SQLDBA> select sid,serial#,username,status,server from v$session;
SID SERIAL# USERNAME STATUS SERVER
---------- ---------- ------------------------------ -------- ---------
1 1 ACTIVE DEDICATED
2 1 ACTIVE DEDICATED
3 1 ACTIVE DEDICATED
4 1 ACTIVE DEDICATED
5 1 ACTIVE DEDICATED
7 11 SCOTT KILLED PSEUDO
9 11 SYS ACTIVE DEDICATED
7 rows selected.
SQLDBA>
Note that the STATUS of SCOTT's session is now "KILLED". This means
that his session has been marked killed by Oracle and he can no longer
initiate database calls. It also means that all the resources he may
have had locked (table locks, tx locks, etc) have been freed.
Also note that the SERVER column for SCOTT's session is now "PSEUDO".
This means that his session is no longer being served by his server
process anymore. His session is now being served by the 'pseudo-process'
which is a dummy process Oracle maintains to store dead or detached
sessions. The pseudo-process does NOT have an associated operating
system process -- it is merely an object stored in shared memory which
is used by Oracle to cleanup sessions.
So, we've succeeded in killing SCOTT's session, but it still appears in
the v$session table. This is because his session state object has not
yet been deleted (though all it's associated resources have). The reason
for this is because SCOTT's process is still alive and thinks it still has
a session associated with it. So, when SCOTT again tries to do some SQL
his process needs to look at it's session, notice that it's been killed,
and inform SCOTT of this fact.
From SCOTT's point of view, this is what he sees --
SQLDBA> connect scott/tiger
Connected.
SQLDBA>
At this point in time, we killed him ... now, he returns and tries to
perform a query:
SQLDBA> select * from emp;
select * from emp
*
ORA-00028: your session has been killed
SQLDBA>
SQLDBA> select * from emp;
ORA-01012: not logged on
SQLDBA>
And he gets an ORA-28 saying his session has been killed. Any further
attempts at querying the database return ORA-1012 saying he is not logged
on. SCOTT can of course login again and he will be allocated a new
session and he will be able to work as usual.
Now the DBA looks again at v$session and sees SCOTT's session is truly
gone:
SQLDBA> select sid,serial#,username,status,server from v$session;
SID SERIAL# USERNAME STATUS SERVER
---------- ---------- ------------------------------ -------- ---------
1 1 ACTIVE DEDICATED
2 1 ACTIVE DEDICATED
3 1 ACTIVE DEDICATED
4 1 ACTIVE DEDICATED
5 1 ACTIVE DEDICATED
9 11 SYS ACTIVE DEDICATED
6 rows selected.
SQLDBA>
B. Killing Active Sessions
When killing an active session, the behavior is more straightforward.
Example --
SQLDBA> select sid,serial#,username,status,server from v$session;
SID SERIAL# USERNAME STATUS SERVER
---------- ---------- ------------------------------ -------- ---------
1 1 ACTIVE DEDICATED
2 1 ACTIVE DEDICATED
3 1 ACTIVE DEDICATED
4 1 ACTIVE DEDICATED
5 1 ACTIVE DEDICATED
7 11 SCOTT ACTIVE DEDICATED
9 11 SYS ACTIVE DEDICATED
7 rows selected.
SQLDBA>
SQLDBA> alter system kill session '7,11';
Statement processed.
SQLDBA> select sid,serial#,username,status,server from v$session;
SID SERIAL# USERNAME STATUS SERVER
---------- ---------- ------------------------------ -------- ---------
1 1 ACTIVE DEDICATED
2 1 ACTIVE DEDICATED
3 1 ACTIVE DEDICATED
4 1 ACTIVE DEDICATED
5 1 ACTIVE DEDICATED
9 11 SYS ACTIVE DEDICATED
6 rows selected.
SQLDBA>
Here, we can see that the session was killed immediately and deleted
immediately. This is the case because SCOTT was interrupted by an ORA-28
error and, realizing that it's session was killed, SCOTT's process deleted
it's own session state object. The ALTER SYSTEM KILL SESSION command will
wait (up to 60 seconds -- see Section C below) until the session has been
deleted before returning.
From SCOTT's point of view,
SQLDBA> connect scott/tiger
Connected.
SQLDBA> select * from emp;
select * from emp
*
ORA-00028: your session has been killed
SQLDBA> select * from emp;
ORA-01012: not logged on
SQLDBA>
C. Marked for Kill
It is sometimes possible that the session to be killed is busy doing
some activity that cannot be interrupted (network i/o, rollback, etc).
In this case, if the uninterruptable activity lasts for more than a
minute, ALTER SYSTEM KILL SESSION will return ORA-31 saying that the
session has been marked killed, but it won't be deleted until the
uninterruptable activity is finished. Thus, the session's resources
will not be freed until then also.
The ALTER SYSTEM KILL SESSION command may take up to 60 seconds to
return if it is waiting for the session to finish some activity.
Example --
SQLDBA> select sid,serial#,username,status,server from v$session;
SID SERIAL# USERNAME STATUS SERVER
---------- ---------- ------------------------------ -------- ---------
1 1 ACTIVE DEDICATED
2 1 ACTIVE DEDICATED
3 1 ACTIVE DEDICATED
4 1 ACTIVE DEDICATED
5 1 ACTIVE DEDICATED
7 11 SCOTT ACTIVE DEDICATED
9 11 SYS ACTIVE DEDICATED
7 rows selected.
SQLDBA>
SQLDBA> alter system kill session '7,11';
ORA-00031: session marked for kill
SQLDBA> select sid,serial#,username,status,server from v$session;
SID SERIAL# USERNAME STATUS SERVER
---------- ---------- ------------------------------ -------- ---------
1 1 ACTIVE DEDICATED
2 1 ACTIVE DEDICATED
3 1 ACTIVE DEDICATED
4 1 ACTIVE DEDICATED
5 1 ACTIVE DEDICATED
7 11 SCOTT KILLED DEDICATED
9 11 SYS ACTIVE DEDICATED
7 rows selected.
SQLDBA>
Note that SCOTT's session is marked "KILLED" above, but it's server
is still "DEDICATED". This indicates that as soon as his session's
current activity is finished, it will terminate itself.
III. TECHNICAL DOCUMENTATION
A. IMPLEMENTATION
The ALTER SYSTEM KILL SESSION command is implemented almost entirely by
ksukil() in ksu.c. Here is the documentation for that function --
void ksukil(/*_ text *idstr _*/);
/*
ksukil - Kernel Service User KILl user session
idstr = text string of the form "index,serial"
index - the session's index number
serial - the session's serial number
Kills the specified user session by interrupting it's server (if any) and
forces the session state object to get deleted.
During the kill, the session state object undergoes the following state
changes --
1) Marked for kill (KSUSFKIL) -- as soon as we are committed to kill this
session, we set this bit saying it is marked for kill. This means that
any further calls to ksupuc() by a server of this session will return
OER-28 saying this session was killed. Thus, sessions in this state
cannot initiate new calls.
The remaining state changes depend on whether the session is active in a
call or not. If the session *is* active in a call, the following occurs:
2) The killer signals an interrupt in the server of the session and waits
for the session to complete its call. In the normal case, this will be
quick since the call should terminate with the interrupted error. Then,
the server will notice that it was interrupted and perform a normal
"logoff" deleting the session.
OR, if the session is *not* in a call (idle):
2) The killer marks the session for "idle kill". This sets the KSUSFIKL
bit saying that no one can delete this session now except for PMON.
The killer then moves the session state object to the pseudo-process so
PMON will notice it there and delete all the children of the session.
The session object itself is not yet deleted, though, until the server
has cleaned up PGA pointers to it.
3) Once the server of the session becomes active again (by calling ksupuc),
it sees that the session was marked for kill and cleans up its PGA
pointers to that session (see ksuded). When it has finished cleaning
up these pointers, it sets the KSUSFIKD bit in the session. This
indicates that the session state object can now be deleted.
4) Finally, PMON sees that the KSUSFIKD bit is set in the session (still
hanging off the pseudo process) and deletes the session object itself.
It is possible that the session to be killed is currently blocked in a
network call or in some other uninterruptable state -- if it remains that
way for more than 60 seconds, ksukil will return OER-31 to the killer.
When this happens, the session will kill itself when it finally comes out
of its uninterruptable call.
*/
Other functions involved in the kill --
opiodr() - checks for the OER-28 in it's ONERROR frame and if it
finds that error, deletes the session by simulating a logoff.
ksuded() - now used for both the idle sniper (resource limits) as well as
kill session; cleans up PGA pointers to session and sets bit
indicating to PMON that the session can be deleted.
ksupuc() - check if marked for kill and call ksuded if so.
ksuxdp() - look for killed sessions on the pseudo-process and delete them
and/or their children as required.
B. DEBUGGING
If you are having issues with killing sessions it is recommended that you
set the following events:
# ksu debugging:
# (bit 1): Enable ksdwrf tracing of the kill session code path. There is
# some tracing for both the killer and the killed session.
# (bit 2): Assert if interrupt queue full. ORA-28 signals may get dropped
# because the target process's interrupt queue is full.
event="10422 trace name context forever, level 3" # ksu debugging
# Every time an ORA-28 is cleared by the killed process this event will force
# a stack dump.
event="10423 trace name context forever, level 28" # trace clears of ORA-28
页:
[1]