Oracle中Kill session的研究
时间:2007-12-23 来源:不详 作者:迈克DB
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;
USERNAMESTATUSADDRKSLLAPSCKSLLAPSNKSLLASPOKSLLID1RKSD
-------------------------------------------------------------------------------------------
542B44A8000
ACTIVE542B48581142406901
ACTIVE542B4C0826161590101
ACTIVE542B4FB87462408301
ACTIVE542B536812152408101
ACTIVE542B571815462408301
ACTIVE542B5AC87941592301
ACTIVE542B5E7850162408501
ACTIVE542B6228754152408101
ACTIVE542B65D81142406901
ACTIVE542B69882301457101
USERNAMESTATUSADDRKSLLAPSCKSLLAPSNKSLLASPOKSLLID1RKSD
-------------------------------------------------------------------------------------------
上一篇:Rman操作简单分析 下一篇:如何查询redo logfile的使用率
文章评论
共有位Admini5网友发表了评论 查看完整内容