This Domain(Admin5.com) is for Sale:

Oracle中Kill session的研究

时间:2007-12-23  来源:不详  作者:迈克DB
我们知道,在Oracle数据库中,可以通过killsession的方式来终止一个进程,其基本语法结构为:
altersystemkillsession’sid,serial#’;

被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.

我们发现当一个session被kill掉以后,该session的paddr被修改,假如有多个session被kill,那么多个session的paddr都被更改为相同的进程地址:
SQL>selectsaddr,sid,serial#,paddr,username,statusfromv$sessionwhereusernameisnotnull;

SADDRSIDSERIAL#PADDRUSERNAMESTATUS
--------------------------------------------------------------------------
542E0E6C11314542B70E8EYGLEINACTIVE
542E504418662542B6D38SYSACTIVE


SQL>altersystemkillsession’11,314’;

Systemaltered.

SQL>selectsaddr,sid,serial#,paddr,username,statusfromv$sessionwhereusernameisnotnull;

SADDRSIDSERIAL#PADDRUSERNAMESTATUS
--------------------------------------------------------------------------
542E0E6C11314542D6BD4EYGLEKILLED
542E504418662542B6D38SYSACTIVE

dedecms.com


SQL>selectsaddr,sid,serial#,paddr,username,statusfromv$sessionwhereusernameisnotnull;

SADDRSIDSERIAL#PADDRUSERNAMESTATUS
--------------------------------------------------------------------------
542E0E6C11314542D6BD4EYGLEKILLED
542E2AA414397542B7498EQSPINACTIVE
542E504418662542B6D38SYSACTIVE

SQL>altersystemkillsession’14,397’;

Systemaltered.

SQL>selectsaddr,sid,serial#,paddr,username,statusfromv$sessionwhereusernameisnotnull;

SADDRSIDSERIAL#PADDRUSERNAMESTATUS
--------------------------------------------------------------------------
542E0E6C11314542D6BD4EYGLEKILLED
542E2AA414397542D6BD4EQSPKILLED
542E504418662542B6D38SYSACTIVE


在这种情况下,很多时候,资源是无法释放的,我们需要查询spid,在操作系统级来kill这些进程.但是由于此时v$session.paddr已经改变,我们无法通过v$session和v$process关联来获得spid那还可以怎么办呢?
我们来看一下下面的查询:
SQL>SELECTs.username,s.status,
2x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP, 织梦内容管理系统
3decode(bitand(x.ksuprflg,2),0,null,1)
4FROMx$ksuprx,v$sessions
5WHEREs.paddr( )=x.addr
6andbitand(ksspaflg,1)!=0;


USERNAMESTATUSADDRKSLLAPSCKSLLAPSNKSLL
ASPOKSLLID1RKSD
-------------------------------------------------------------------------------------------
542B44A8000
ACTIVE542B48581142406901
ACTIVE542B4C0826161590101
ACTIVE542B4FB87462408301
ACTIVE542B536812152408101
ACTIVE542B571815462408301
ACTIVE542B5AC87941592301
ACTIVE542B5E7850162408501
ACTIVE542B6228754152408101
ACTIVE542B65D81142406901
ACTIVE542B69882301457101

USERNAMESTATUSADDRKSLLAPSCKSLLAPSNKSLL
ASPOKSLLID1RKSD
-------------------------------------------------------------------------------------------

看完这篇,您有何感觉呢?

文章评论

共有位Admini5网友发表了评论 查看完整内容

24小时热门信息