Oracle資料庫健康檢查常用SQL

1 檢查資料庫版本
select * from v$version;
2 檢查例項基本情況
select instance_name,host_name,status,archiver from v$instance;
3 檢查安裝的元件
select * from v$option where value='TRUE';
4 檢查初始化引數
select name,value from v$parameter;
5 檢查statistics_level的值
show parameter statistics_level
6 檢查是否處於歸檔模式及歸檔路徑
archive log list;

[@more@]7 檢查控制檔案狀態
select status,name,block_size,file_size_blks from v$controlfile;
8 檢查日誌檔案狀態
select group#,thread#,sequence#,bytes,members,archived,status,first_change# from v$log;
9 檢查日誌檔案的物理狀態
select group#,status,type,member from v$logfile;
10 檢查日誌檔案上是否存在IO競爭
Select event,total_waits,time_waited,average_wait from v$system_event where event like 'log file switch completion%';
11 檢查資料檔案的狀態
select name,bytes,status,enabled from v$datafile;

select name,bytes,status,enabled from v$tempfile;
12 資料檔案的IO情況：
select substr(C.file#,1,2) "#", substr(C.name,1,50) "Name", C.status, C.bytes, D.phyrds, D.phywrts from v$datafile C, v$filestat D where C.file# = D.file#;
13 檢查最大會話情況
select sessions_current,sessions_highwater,cpu_count_current from v$license;
14 檢查表空間狀態
select tablespace_name,status from dba_tablespaces;
15 檢查表空間使用情況
select b.file_id 檔案ID號, b.tablespace_name 表空間名, b.bytes 位元組數,
(b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩餘空間,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩餘百分比 from dba_free_space a,dba_data_files b
where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;
16 檢查臨時表空間使用情況
select username,temporary_tablespace from dba_users;
17 檢查UNDO表空間引數配置
show parameter undo

18 檢查回滾段爭用情況
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;
19 檢測當前的鎖衝突
select sid,serial#,username from v$session where sid in (select blocking_session from v$session);

SQL> alter system kill session '159,24' immediate;
20 檢查SGA記憶體分配情況
select component,current_size,granule_size from v$sga_dynamic_components;
21 緩衝區快取的診斷
select name,value from v$sysstat where name in('db block gets','consistent gets','physical reads');

select ROUND((1-(PHY.VALUE/(CUR.VALUE+CON.VALUE)))*100,1)||'%' RATIO
FROM V$SYSSTAT PHY,V$SYSSTAT CUR,V$SYSSTAT CON
WHERE PHY.NAME ='physical reads' AND CUR.NAME = 'db block gets' AND CON.NAME = 'consistent gets';　　　90%
22 庫快取的診斷
select namespace,gets,gethits,gethitratio from v$librarycache;
select namespace,pinhitratio from v$librarycache;　　90%

select sum(bytes)/1024/1024 used from v$sgastat where pool='shared pool' and name<>'free memory';
共享池利用率＝60.1 / 76 = 0.79　　　60~80%

23 檢查字典緩衝區快取
select (sum(getmisses)/sum(gets))*100 getmisses_ratio from v$rowcache where gets>0;　　　15%
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

24 檢查日誌緩衝區的缺失率
SELECT name, gets, misses, Decode(gets,0,0,misses/gets*100) ratio1, immediate_gets, immediate_misses, Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');　　　1%
25 檢查PAG引數配置
show parameter pga
26 檢查PGA使用情況
Select name,value,unit from v$pgastat;
27 檢查閃回區使用情況
select name,space_limit,space_used from v$recovery_file_dest;
28 檢查後臺進行使用情況
select name,description from v$bgprocess where paddr !='00';
29 收集告警日誌資訊
複製出alter.log及相應的跟蹤日誌檔案
30 備份控制檔案指令碼
alter database backup controlfile to trace;
生成的檔案在相應目錄下 D:oracleproduct10.2.0adminorcludump
31 建立PFILE檔案並備份
Show parameter spfile;
Create pfile from spfile;
32 檢查當前使用者具有的許可權
select * from user_tab_privs;
select * from user_sys_privs;
select * from user_role_privs;
33 檢查使用者具有哪些表
select * from user_tables;
34 檢查使用者索引的情況
select index_name,index_type,table_owner,tablespace_name,leaf_blocks,status from user_indexes;
35 檢查當前使用者的表是否存在行連結
select table_name,chain_cnt from user_tables;
36 執行addm或ash或awr報告：
[oracle@alydb ~]$ cd $ORACLE_HOME
[oracle@alydb db_1]$ cd rdbms/admin
[oracle@alydb admin]$ sqlplus /nolog
conn /as sysdba
Connected.
SQL> @addmrpt

SQL> @ashrpt

SQL> @awrrpt