杀掉oracle,如何快速的杀掉Oracle的Session

系统管理员 2023-01-18 04:29 252阅读 0赞

\--如何快速的杀掉Oracle的Session

\--1.如何查看session级的等待事件？

Select

s.username,s.program,s.status,se.event,se.total\_waits,se.total\_timeouts,se.time\_waited,se.average\_wait

from v$session s, v$session\_event se Where s.sid=se.sid And

se.event not like 'SQl\*Net%' And s.status ='ACTIVE' And s.username

is not null

SELECT

A.OWNER,

A.OBJECT\_NAME,

B.XIDUSN,

B.XIDSLOT,

B.XIDSQN,

B.SESSION\_ID,

B.ORACLE\_USERNAME,

B.OS\_USER\_NAME,

B.PROCESS,

B.LOCKED\_MODE,

C.MACHINE,

C.STATUS,

C.SERVER,

C.SID,

C.SERIAL\#,

C.PROGRAM

FROM

ALL\_OBJECTS A, V$LOCKED\_OBJECT B, SYS.GV\_$SESSION

WHERE

(A.OBJECT\_ID = B.OBJECT\_ID)

AND

(B.PROCESS = C.PROCESS)

ORDER

BY 1, 2

\--释放session

Sql:

alter

system kill session 'sid, serial\#'

alter

system kill session '30, 2412';

SELECT

se.sid,

se.serial\#,

pr.SPID,

se.username,

se.status,

se.terminal,

se.program,

se.MODULE,

se.sql\_address,

st.event,

st.p1text,

si.physical\_reads,

si.block\_changes

FROM

v$session se, 　v$session\_wait st, v$sess\_io si, v$process

WHERE

st.sid = se.sid 　AND st.sid = si.sid

AND

se.PADDR = pr.ADDR

AND

se.sid > 6　AND st.wait\_time = 0

AND

st.event NOT LIKE '%SQL%'

ORDER

BY physical\_reads DESC

select

a.sid,spid,status,substr(a.program,1,40)

prog,a.terminal,osuser,value/60/100 value

from

v$session a,v$process b,v$sesstat c

where

c.statistic\#=12 and c.sid=a.sid and a.paddr=b.addr order by value

desc;

select

sys.v\_$session.osuser,sys.v\_$session.machine,v$lock.sid,

sys.v\_$session.serial\#,

decode(v$lock.type,

'MR',

'Media Recovery',

'RT','Redo

Thread',

'UN','User

Name',

'TX',

'Transaction',

'TM',

'DML',

'UL',

'PL/SQL User Lock',

'DX',

'Distributed Xaction',

'CF',

'Control File',

'IS',

'Instance State',

'FS',

'File Set',

'IR',

'Instance Recovery',

'ST',

'Disk Space Transaction',

'TS',

'Temp Segment',

'IV',

'Library Cache Invalida-tion',

'LS',

'Log Start or Switch',

'RW',

'Row Wait',

'SQ',

'Sequence Number',

'TE',

'Extend Table',

'TT',

'Temp Table',

'Unknown')

LockType,

rtrim(object\_type)

|| ' ' || rtrim(owner) || '.' || object\_name

object\_name,

decode(lmode, 0,

'None',

'Null',

'Row-S',

'Row-X',

'Share',

'S/Row-X',

'Exclusive', 'Unknown') LockMode,

decode(request,

0, 'None',

'Null',

'Row-S',

'Row-X',

'Share',

'S/Row-X',

'Exclusive', 'Unknown') RequestMode,

ctime, block

from

v$lock, all\_objects, sys.v\_$session

where

v$Lock.sid > 6 and sys.v\_$session.sid =

v$lock.sid

and

v$lock.id1 = all\_objects.object\_id;

\--1

查询session被锁的sql,简要查询,得到SID

select

object\_name,machine,s.sid,s.serial\#

from

v$locked\_object l,dba\_objects o ,v$session s

where

l.object\_id　=　o.object\_id and l.session\_id=s.sid;

\--2

使用alter system kill session '24,111';

(其中24,111分别是上面查询出的sid,serial\#)进行释放

alter

system kill session '30, 2412'

\--3

执行下面的语句获得进程(线程)号,sid为第一步查询出的sid号：

select

spid, osuser, s.program

from

v$session s,v$process p

where

s.paddr=p.addr and s.sid=30;

杀掉oracle,如何快速的杀掉Oracle的Session

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle session status killed,进程状态为KILLED的进程如何杀掉

相关杀掉oracle,如何快速的杀掉Oracle的Session

相关 InnoDB如何快速杀掉堵塞会话的思考

相关如何杀掉D状态的进程？[zt]

相关批量杀掉进程

相关 linux 远程杀掉进程

相关 Hadoop 查看/杀掉 Job

相关 oracle几条杀掉数据库连接的相关SQL语句

相关如何杀掉mysql进程

相关 Ubuntu下杀掉进程