ORACLE DBA 运维常用查询

数据库当前哪些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;