oracle数据库性能健康检查脚本VIP专享VIP免费

*******************os 层检查 #检查oracle 进程 ps -ef|grep ora|wc -l crs_stat -t -v top glance crsctl check cluster (cssd) vmstat 3600 6 ***********************查看基本信息 #database 概况信息检查 #检查database 基本信息 select * from v$version; select name ,open_mode,log_mode from v$database; select instance_number,instance_name ,status from gv$instance; show parameter cpu_c show parameter block_size select group#,thread#,members,bytes/1024/1024 from gv$log; show sga select count(*) from v$controlfile select count(*) from v$tempfile; select count(*) from v$datafile; ********************************查看数据文件信息 #检查表空间数据文件信息 col tablespace_name for a30 select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name; #检查表空间 SELECT TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES; #检查数据文件状态 select count(*),status from v$datafile group by status; #检查表空间使用情况 select f.tablespace_name, a.total, f.free,(a.total-f.free)/1024 "used SIZE(G)" ,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" / #查询临时 segment 使用情况 COL username FORMAT a10; COL segtype FORMAT a10; SELECT username, segtype, extents "Extents Allocated" ,blocks "Blocks Allocated" FROM v$tempseg_usage; #查看数据文件信息,若文件较多可以根据需要字段进行排序 输出 top 10 SELECT fs.phyrds "Reads", fs.phywrts "Writes" ,fs.avgiotim "Average I/O Time", df.name "Datafile" FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#; #查看所有数据文件i/o 情况,若文件太多,可以...