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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16712909/viewspace-1022667/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16712909/viewspace-1022667/