数据库当前哪些ACTVIE进程
select a.CLIENT_INFO,
'alter system kill session ''' || a.sid || ',' || a.serial# || ''';' killSQL ,
b.spid,
a.sid,
round(c.value / 60 / 100, 2) CPUUSED,
d.SQL_TEXT,
d.SQL_FULLTEXT,
a.SQL_ID,
substr(a.program, 1, 48) prog,
substr(a.module, 1, 48) mod,
a.machine,
substr(a.action, 1, 48) action,
a.terminal,
a.osuser,
a.status,
a.EVENT
from v$session a, v$process b, v$sesstat c, v$sqlarea d
where c.statistic# = 12
and c.sid = a.sid
and a.paddr = b.addr
and a.status = 'ACTIVE'
AND A.TYPE = 'USER'
and a.SQL_ID = d.SQL_ID /* and SQL_TEXT like 'SELECT * FROM( SELECT X.* , ROWNUM RN%'*/
order by value desc;
编译包卡死的时候查:
SELECT distinct 'alter system kill session ''' || b.sid || ',' || b. SERIAL# || ''';' killSQL,
c.KGLNAOBJ PNAME,
b.MACHINE,b.INST_ID,
'kill -9 '||d.spid killspid
FROM SYS.x$kglpn a, gv$session b, SYS.x$kglob c,gv$process d
WHERE a.KGLPNUSE = b.saddr
And upper(c.KGLNAOBJ) Like 'PKG%'
and a.KGLPNHDL = c.KGLHDADR
and b.paddr=d.addr;
锁等待
SELECT a.inst_id,
b.sid,
b.SERIAL#,
c.spid,
'alter system kill session ''' || b.sid || ',' || b.SERIAL# || ''';' KillSQL,
DECODE(a.request, 0, 'Holder: ', 'Waiter: ') || a.sid sess,
(SELECT SQL_TEXT
FROM V$SQL
WHERE SQL_ID = b.SQL_ID
AND ROWNUM = 1) BLOCKER_SQL_TEXT,
(SELECT
SQL_FULLTEXT
FROM V$SQL
WHERE SQL_ID = b.SQL_ID
AND ROWNUM = 1) FULL_SQL_TEXT,
a.lmode,
b.EVENT,
b.STATUS,
b.sql_id,
b.machine,
a.id1,
a.id2,
a.request,
a.type,
b.P1TEXT,
b.p1,
b.p2text,
b.p2,
b.P3TEXT,
b.p3,
b.taddr
FROM gV$LOCK a, gv$session b, gv$process c
WHERE (a.id1, a.id2, a.type) IN
(SELECT c.id1, c.id2, c.type FROM gV$LOCK c WHERE c.request > 0)
and NVL(b.program, 'AA') not like 'oracle@%'
and a.INST_ID = b.INST_ID
and a.SID = b.SID
and b.INST_ID = c.INST_ID(+)
and b.PADDR = c.ADDR(+) /*and a.inst_id=2*/
ORDER BY a.id1, a.request;