Aries的IT部落格

杀掉状态为Inactive状态的死进程：
select   sid,   serial# , username,   paddr,   status   from   v$session       
where   username   =   '用户名 '   and       
status   =   'INACTIVE ';     


oracle级kill掉该session：
alter system kill session ‘&sid,&serial#‘;

查询目前锁对象信息
select sid,
       serial#,
       username,
       SCHEMANAME,
       osuser,
       MACHINE,     
       terminal,
       PROGRAM,
       owner,
       object_name,
       object_type,
       o.object_id
  from dba_objects o, v$locked_object l, v$session s
 where o.object_id = l.object_id
   and s.sid = l.session_id;

检查表空间使用情况
select f.tablespace_name,
a.total,
f.free,
round((f.free / a.total) * 100) "% Free"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";

检查数据库的等待事件
set pages 80

set lines 120

col event for a40

select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
  from v$session_wait
 where event not like ‘SQL%‘
   and event not like ‘rdbms%‘;

Disk Read最高的SQL语句的获取
SELECT SQL_TEXT
FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM <= 5;

查找前十条性能差的sql
SELECT *
FROM (SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM V$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;

等待时间最多的5个系统等待事件的获取
SELECT *
  FROM (SELECT *
          FROM V$SYSTEM_EVENT
         WHERE EVENT NOT LIKE ‘SQL%‘
         ORDER BY TOTAL_WAITS DESC)
 WHERE ROWNUM <= 5;

检查运行很久的SQL
COLUMN USERNAME FORMAT A12

COLUMN OPNAME FORMAT A16

COLUMN PROGRESS FORMAT A8

SELECT USERNAME,
       SID,
       OPNAME,
       ROUND(SOFAR * 100 / TOTALWORK, 0) || ‘%‘ AS PROGRESS,
       TIME_REMAINING,
       SQL_TEXT
  FROM V$SESSION_LONGOPS, V$SQL
 WHERE TIME_REMAINING <> 0
   AND SQL_ADDRESS = ADDRESS
   AND SQL_HASH_VALUE = HASH_VALUE;

检查消耗CPU最高的进程
SET LINE 240

SET VERIFY OFF

COLUMN SID FORMAT 999

COLUMN PID FORMAT 999

COLUMN S_# FORMAT 999

COLUMN USERNAME FORMAT A9 HEADING "ORA USER"

COLUMN PROGRAM FORMAT A29

COLUMN SQL FORMAT A60

COLUMN OSNAME FORMAT A9 HEADING "OS USER"

SELECT P.PID PID,
       S.SID SID,
       P.SPID SPID,
       S.USERNAME USERNAME,
       S.OSUSER OSNAME,
       P.SERIAL# S_#,
       P.TERMINAL,
       P.PROGRAM PROGRAM,
       P.BACKGROUND,
       S.STATUS,
       RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P,
       V$SESSION S,
       V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE ‘%&1%‘;

检查表空间的I/O比例
SELECT DF.TABLESPACE_NAME NAME,
       DF.FILE_NAME       "FILE",
       F.PHYRDS           PYR,
       F.PHYBLKRD         PBR,
       F.PHYWRTS          PYW,
       F.PHYBLKWRT        PBW
  FROM V$FILESTAT F, DBA_DATA_FILES DF
 WHERE F.FILE# = DF.FILE_ID
 ORDER BY DF.TABLESPACE_NAME;