oracle 检查数据库状态（命令验证中）

lsnrctl status
查看数据库实例状态
复制

 srvctl status database -d orcl
复制

[grid@db1 ~]$ crsctl check crs
复制

[grid@db1 ~]$  crs_stat -t -v
复制

set head off
set pagesize 0
set linesize 1000
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
复制

select sequence#,status from v$log;
复制

SELECT sessions_current,sessions_highwater FROM v$license; 
复制

SELECT inst_id,username,COUNT(*) FROM gv$session GROUP BY inst_id,username;  
复制

• 查看ORACLE实例状态 

SELECT INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,DATABASE_STATUS FROM V$INSTANCE;
复制

•  查看正在运行的JOB

SELECT * FROM DBA_JOBS_RUNNING;
复制

• 检查数据库JOB的完成情况

SELECT JOB, LOG_USER, LAST_DATE, NEXT_DATE, WHAT, FAILURES  FROM DBA_JOBS;
复制

• 查看正在运行的 JOB 的 SID、SERIAL#、SPID

SELECT A.JOB, B.SID, B.SERIAL#, C.SPID FROM DBA_JOBS_RUNNING A, V$SESSION B, V$PROCESS C  WHERE A.SID = B.SID AND B.PADDR = C.ADDR AND JOB = '21';
复制

• 通过SID查询SERIAL#、SPID

SELECT B.SID, B.SERIAL#, C.SPID   FROM V$SESSION B, V$PROCESS C  WHERE B.SID = '554' AND B.PADDR = C.ADDR;
复制

• 查找前十条性能差的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
复制

• 检查死锁

SELECT   bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",ws.program "Waiting App", bs.machine "Blocking Machine",ws.machine "Waiting Machine", bs.osuser "Blocking OS User",ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.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 Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,          DECODE
(hk.BLOCK,
0, 'NOT Blocking',         /**//* Not blocking any other processes */
1, 'Blocking',             /**//* This lock blocks other processes */
2, 'Global',          /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws    WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE( ) = hk.TYPE
AND wk.id1( ) = hk.id1
AND wk.id2( ) = hk.id2
AND hk.SID = bs.SID( )
AND wk.SID = ws.SID( )
AND (bs.username IS NOT NULL)
AND (bs.username  'SYSTEM')
AND (bs.username  'SYS')
ORDER BY 1;
复制

查看当前会话

SELECT SID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUS FROM V$SESSION;
复制

查看DDL锁

SELECT *
FROM DBA_DDL_LOCKS
WHERE OWNER = 'FWYANG';
复制
复制

检查等待事件

SELECT SID, A.USERNAME, EVENT, WAIT_CLASS, T1.SQL_TEXT
FROM V$SESSION A, V$SQLAREA T1
WHERE WAIT_CLASS  'Idle'
AND A.SQL_ID = T1.SQL_ID;
复制

检查数据文件状态

SELECT FILE_NAME,STATUS FROM DBA_DATA_FILES;**粗体**
复制

检查表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"  FROM (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,  ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES  FROM SYS.DBA_FREE_SPACE  GROUP BY TABLESPACE_NAME) F,  (SELECT DD.TABLESPACE_NAME,  ROUND(SUM(DD.BYTES) / (10http://www.mianfeiwendang.com/doc/dba9a779825b68483416ac9624 * 1024), 2) TOT_GROOTTE_MB  FROM SYS.DBA_DATA_FILES DD  GROUP BY DD.TABLESPACE_NAME) D  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME  ORDER BY 4 DESC;
复制

查看查询语句

SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,        SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE  WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
复制

查看表的空间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;
复制

检查文件系统的 I/O 比例

SELECT SUBSTR(A.FILE#, 1, 2) "#",
SUBSTR(A.NAME, 1, 30) "NAME",
A.STATUS,
A.BYTES,
B.PHYRDS,
B.PHYWRTS
FROM V$DATAFILE A, V$FILESTAT B
WHERE A.FILE# = B.FILE#;
复制

检查CPU最高的进程

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
FROM V$PROCESS P, V$SESSION S, V$SQLAREA A
WHERE P.ADDR = S.PADDR
AND S.SQL_ADDRESS = A.ADDRESS( )
AND P.SERIAL#  '1';
复制

查看运行时间的SQL

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;
复制

等待时间最多的5个系统等待事件的获取

SELECT *
FROM (SELECT *
FROM V$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL%'
ORDER BY TOTAL_WAITS DESC)
WHERE ROWNUM
复制