select 'alter system kill session '''||sess.sid||','||sess.serial#||''' immediate;',ao.object_name,sess.* from v$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid and sess.OSUSER='root' order by sess.LOGON_TIME;
select 'alter system kill session '''||sess.sid||','||sess.serial#||''' immediate;',ao.object_name from gv$locked_object lo,dba_objects ao,gv$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
select 'alter system kill session ''' || t.SID || ',' || t.SERIAL# ||
''' immediate;',c.SPID,t.MACHINE,t.LOGON_TIME,t.*
from gv$session t ,gv$process c
where t.USERNAME = 'CTCES'
and t.paddr = c.addr
and t.EVENT = 'enq: TX - row lock contention' order by t.LOGON_TIME
Oracle连接管理及高耗cpu sql语句的捕捉
当前连接查询(按照连接客户端分组)
select s.MACHINE,count(*) from v$session s group by s.MACHINE
立即断开某客户端的连接的SQL语句获取
select 'alter system kill session '''||s.SID||','||s.SERIAL#||''' immediate;' from v$session s where machine='aca800a0.ipt.aol.com'
高耗cpu sql语句的捕捉
通过top命令(AIX使用topas) 找出耗CPU最高的SPID ,然后以这个SPID为参数,查最耗CPU的SQL和断开SPID的连接语句
SELECT 'alter system kill session '''||b.SID||','||b.SERIAL#||''' immediate;',c.SPID,a.*
from v$sqlarea a, v$session b ,v$process c
where a.address = decode(b.SQL_ADDRESS,'00',b.prev_sql_addr,b.SQL_ADDRESS ) and b.paddr = c.addr
and c.SPID = :SPID
where ao.object_id = lo.object_id
and lo.session_id = sess.sid and sess.OSUSER='root' order by sess.LOGON_TIME;
select 'alter system kill session '''||sess.sid||','||sess.serial#||''' immediate;',ao.object_name from gv$locked_object lo,dba_objects ao,gv$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
select 'alter system kill session ''' || t.SID || ',' || t.SERIAL# ||
''' immediate;',c.SPID,t.MACHINE,t.LOGON_TIME,t.*
from gv$session t ,gv$process c
where t.USERNAME = 'CTCES'
and t.paddr = c.addr
and t.EVENT = 'enq: TX - row lock contention' order by t.LOGON_TIME
Oracle连接管理及高耗cpu sql语句的捕捉
当前连接查询(按照连接客户端分组)
select s.MACHINE,count(*) from v$session s group by s.MACHINE
立即断开某客户端的连接的SQL语句获取
select 'alter system kill session '''||s.SID||','||s.SERIAL#||''' immediate;' from v$session s where machine='aca800a0.ipt.aol.com'
高耗cpu sql语句的捕捉
通过top命令(AIX使用topas) 找出耗CPU最高的SPID ,然后以这个SPID为参数,查最耗CPU的SQL和断开SPID的连接语句
SELECT 'alter system kill session '''||b.SID||','||b.SERIAL#||''' immediate;',c.SPID,a.*
from v$sqlarea a, v$session b ,v$process c
where a.address = decode(b.SQL_ADDRESS,'00',b.prev_sql_addr,b.SQL_ADDRESS ) and b.paddr = c.addr
and c.SPID = :SPID