分享一个oracle数据库巡检脚本，欢迎大家使用，希望大家在用的过程中发现脚本中的错误并提出改进意见。
-- 数据库巡检脚本-- 版本号2.1-- 该脚本仅对数据库的做一个初步的巡检，具体的优化方案应结合监控、awr、ash去具体分析-- 设置相关格式SET feedback OFF verify OFF trimspool ON term OFF timing OFF heading ONSET pagesize 100 linesize 500-- 生成的文件 ~/oracle_xj_实例名_日期.txtcolumn inst_name new_value inst_name noprintselect instance_name inst_name from v$instance;column txt_time new_value txt_time noprintselect to_char(sysdate,'YYYYMMDD') txt_time from dual;spool /home/oracle/oracle_xj_&inst_name._&txt_time..txtPROMPT 说明：如果有的条目没有结果，则该条目状态正常PROMPTPROMPT 一、数据库的基本情况PROMPTPROMPT 1.1、数据库基本信息col host_name for a20SELECT dbid,name db_name,instance_name,version,parallel rac,host_name FROM v$database,v$instance;PROMPTPROMPT 1.2、实例状态SELECT instance_number,instance_name,status,host_name FROM gv$instance;PROMPTPROMPT 1.3、数据库运行时间col "runtime" for a25SELECT instance_number,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "startuptime",TRUNC(sysdate - (startup_time)) ||'day '||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time))) ||'hour '||MOD(TRUNC(1440*((SYSDATE-startup_time)-TRUNC(sysdate-startup_time))),60) ||'min '||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-TRUNC(SYSDATE-startup_time))),60) ||'second' "runtime"FROM gv$instance;PROMPTPROMPT 二、数据库的cpu、内存情况PROMPTPROMPT 2.1、cpu情况col STAT_NAME for a20col percent for a10SELECT stat_name,to_char((value*100/(sum(value) over())),90.99) || '%' percent FROM v$osstat where stat_name in ('IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME');PROMPTPROMPT 2.2、SGA及其组件大小PROMPT -参考值 - Buffer Cache Size：70%SGA，Shared Pool Size：15%SGA左右col NAME for a35 col percent for a10with tmp as(select decode(a.value,0,b.value,null,b.value,a.value) sga_size from(SELECT name,value from v$parameter where name='sga_target') a,(SELECT name,value from v$parameter where name='sga_max_size') b)SELECT name,round(bytes/1024/1024) "size(M)",round(sga_size/1024/1024) "sga(M)",round(100*bytes/sga_size,2) || '%' percent FROM v$sgainfo,tmp where name in('Buffer Cache Size','Shared Pool Size');PROMPTPROMPT 2.3、PGA大小PROMPT -参考值 - 30% SGAcol NAME for a35 SELECT name,round(value/1024/1024) "size(M)" FROM v$parameter where name = 'pga_aggregate_target';PROMPTPROMPT 三、检查各文件状态PROMPTPROMPT 3.1、查看参数文件col NAME for a20col value for a50SELECT name,value FROM v$parameter where name = 'spfile';PROMPTPROMPT 3.2、查看控制文件col NAME for a75SELECT name,status FROM v$controlfile;PROMPTPROMPT 3.3、查看在线日志col MEMBER for a50select a.group#,a.status,a.type,a.member,(b.bytes/1024/1024) onl_size_mb,(c.bytes/1024/1024) std_size_mb from v$Logfile a,v$Log b,v$standby_log c where a.group#=b.group#(+) and a.group#=c.group#(+)order by 3,1;PROMPTPROMPT 3.4、检查最近一天日志切换频率select thread#,sequence#,to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,case when minutes<0 then null else minutes end mitutes from(SELECT thread#,sequence#,first_time, round((first_time - lag(first_time) over(order by thread#,first_time)) * 24 * 60,2) minutes FROM v$log_history where first_time > sysdate - 2) where first_time > sysdate - 1 order by thread#,firsttime;PROMPTPROMPT 3.4.1、检查最近一周日志切换select to_char(first_time, 'YYYY.MM.DD') day, to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'999') "00", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'999') "01", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'999') "02", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'999') "03", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'999') "04", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'999') "05", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'999') "06", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'999') "07", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'999') "08", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'999') "09", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'999') "10", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'999') "11", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'999') "12", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'999') "13", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'999') "14", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'999') "15", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'999') "16", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'999') "17", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'999') "18", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'999') "19", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'999') "20", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'999') "21", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'999') "22", to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'999') "23" from v$log_history where first_time > trunc(sysdate) - 7 group by to_char(first_time, 'YYYY.MM.DD') order by 1 desc; PROMPTPROMPT 3.5、查看ASM磁盘组空间col NAME for a20col used_percent for 9999select group_number, name, type, total_mb, usable_file_mb, round(100 - usable_file_mb * decode(type,'EXTERN',1,'NORMAL',3,'HIGH',5) *100 / (total_mb)) used_percent from v$asm_diskgroup; PROMPTPROMPT 3.6、查看表空间的使用情况select tablespace_name,round(used/1024/1024) "used(M)",round(total/1024/1024) "total(M)",to_char(round(used/total * 100,2),'9999990.99') used_percent from(select a.tablespace_name,(a.bytes_alloc-b.bytes_free) used,a.bytes_total total from (SELECT tablespace_name, sum(bytes) bytes_alloc, sum(greatest(bytes,maxbytes)) bytes_total FROM dba_data_files group by tablespace_name) a,(SELECT tablespace_name, sum(bytes) bytes_free FROM dba_free_space group by tablespace_name) bwhere a.tablespace_name=b.tablespace_name) order by 4;PROMPTPROMPT 3.7、查看临时表空间使用情况select tablespace_name,used "used(M)",allocate "allocate(M)",total "total(M)",to_char(round(used/total * 100,2),'990.99') used_percentfrom (SELECT tablespace_name,(select sum(blocks)/128 from v$tempseg_usage) used, round(sum(bytes)/1024/1024) allocate,round(sum(greatest(bytes,maxbytes))/1024/1024) totalFROM dba_temp_files group by tablespace_name);PROMPTPROMPT 3.8、查看临时段使用的情况COL username FOR a20COL segtype FOR a10SELECT username, segtype, extents "Extents Allocated", blocks "Blocks Allocated" FROM v$tempseg_usage order by 1,2; PROMPTPROMPT 3.9、查看数据文件状态col NAME for a30SELECT b.name tablespace_name, a.status, count(*) FROM v$datafile a ,v$tablespace b where a.ts#=b.ts# group by b.name, a.status order by 1;PROMPTPROMPT 3.10、归档日志检查col dest_name for a20col error for a40 col gap_status for a10 SELECT dest_id, dest_name, status, type, error, gap_status FROM v$archive_dest_status; PROMPTPROMPT 3.11、最近一周备份情况select input_type,start_time,status from V$RMAN_BACKUP_JOB_DETAILS where start_time > sysdate-7 order by start_time desc;PROMPTPROMPT 四、检查数据库对象状态PROMPT PROMPT 4.1、查看回滚段状态SELECT status,count(*) FROM dba_rollback_segs group by status; PROMPTPROMPT 4.2、检查是否有禁用约束col owner for a20SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status ='DISABLED' and owner not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN')order by 1,3;PROMPTPROMPT 4.3、检查是否有禁用触发器col owner for a20col taigger_name for a10col table_name for a30col table_name for a30SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED' and owner not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN')order by 1,3; PROMPTPROMPT 4.4、Oracle Job是否有失败col what for a50SELECT job,what,last_date,next_date,failures,broken FROM dba_jobs Where failures>0 or broken='Y'; PROMPTPROMPT 4.5、查看无效的对象col object_name for a40SELECT owner,object_name,object_type,last_ddl_time FROM dba_objects where status != 'VALID' and owner not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN','PUBLIC')order by 1,3; PROMPTPROMPT 4.6、检查失效的索引SELECT owner,index_name,table_name,tablespace_name,status From dba_indexes Where status not in('VALID','N/A') order by 1,3;PROMPTPROMPT 4.7、并行度大于1的索引col owner for a25col table_name for a30col index_name for a30col degree for a10select owner,table_name,index_name,degree from dba_indexes where degree>'1' order by 1,2;PROMPTPROMPT 4.8、索引冗余col owner for a25col table_name for a30col redundant_index for a30col sufficient_index for a30select o1.name owner, x.table_name, n1.name redundant_index, n2.name sufficient_index from sys.icol$ ic1, sys.icol$ ic2, sys.ind$ i1, sys.obj$ n1, sys.obj$ n2, sys.user$ o1, sys.user$ o2, dba_indexes x where ic1.pos# = 1 and ic2.bo# = ic1.bo# and ic2.obj# != ic1.obj# and ic2.pos# = 1 and ic2.intcol# = ic1.intcol# and i1.obj# = ic1.obj# and bitand(i1.property, 1) = 0 and (select max(pos#) * (max(pos#) + 1) / 2 from sys.icol$ where obj# = ic1.obj#) = (select sum(xc1.pos#) from sys.icol$ xc1, sys.icol$ xc2 where xc1.obj# = ic1.obj# and xc2.obj# = ic2.obj# and xc1.pos# = xc2.pos# and xc1.intcol# = xc2.intcol#) and n1.obj# = ic1.obj# and n2.obj# = ic2.obj# and o1.user# = n1.owner# and o2.user# = n2.owner# and x.index_name = n1.name and n1.name not like 'BIN$%' and o1.name not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN') order by 1,2;PROMPTPROMPT 4.9、检查碎片程度高的表PROMPT -找出总大小超过1G，且使用率不足70%的表col used_percent for a15select owner,table_name,used used_mb,total total_mb,round(used * 100/total) || '%' used_percent, num_rows,last_analyzed from (SELECT owner, table_name, ROUND(BLOCKS * 8192/1024/1024) total, ROUND(num_rows * AVG_ROW_LEN/1024/1024) used, num_rows, last_analyzedFROM dba_tables) where total>1000 and round(used * 100/total) < 70order by 1,2; PROMPTPROMPT 4.10、检查一些扩展异常的对象PROMPT -对象的extent数量超过临界值的80%，需要moveSELECT Segment_Name, Segment_Type, TableSpace_Name,(Extents/Max_extents)*100 PercentFrom sys.DBA_SegmentsWhere Max_Extents != 0 and (Extents/Max_extents)>=0.8order By Percent;PROMPTPROMPT 4.11、检查对象的下一扩展与表空间的最大扩展值 PROMPT -表空间的剩余空间已经无法满足对象的下次扩展，需要扩表空间SELECT a.table_name, a.next_extent, a.tablespace_nameFROM all_tables a,(SELECT tablespace_name, max(bytes) as big_chunkFROM dba_free_spacegroup by tablespace_name ) fwhere f.tablespace_name = a.tablespace_nameand a.next_extent > f.big_chunkUNIONSELECT a.index_name, a.next_extent, a.tablespace_nameFROM all_indexes a,(SELECT tablespace_name, max(bytes) as big_chunkFROM dba_free_spacegroup by tablespace_name ) fwhere f.tablespace_name = a.tablespace_nameand a.next_extent > f.big_chunk; PROMPTPROMPT 4.12、查看行迁移或行链接col "Percentage" for a15SELECT 'Chained Rows' "Ratio", ROUND((SELECT value FROM V$SYSSTAT WHERE name = 'table fetch continued row')/ (SELECT value FROM V$SYSSTAT WHERE name = 'table scan rows gotten')* 100, 2)||'%' "Percentage" FROM DUAL;PROMPTPROMPT 4.13、检查system表空间内的内容select owner,table_name object_name,type from(SELECT owner,table_name,'table' as type FROM dba_tableswhere tablespace_name='SYSTEM' andowner!='SYS' and owner not in('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS')UNIONSELECT owner,index_name,'index' as type FROM dba_indexeswhere tablespace_name='SYSTEM' andowner!='SYS' and owner not in('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS')) order by 1,3;PROMPTPROMPT 4.14、检查回收站内的内容SELECT owner, count(*), max(droptime) FROM dba_recyclebin group by owner;PROMPTPROMPT 五、dbtime和连接情况PROMPTPROMPT 5.1、查看当前实例最近一天的db_timeSELECT C.snap_id,to_char(b.begin_interval_time, 'yyyymmddhh24mi') begin_snapshot_time,c.DB_TIME FROM (SELECT a.snap_id, TRUNC((DB_TIME_1 - lag(DB_TIME_1, 1, DB_TIME_1) over(PARTITION BY stat_name,instance_number ORDER BY snap_id)) /1000000 / 60) DB_TIME FROM (SELECT a.snap_id, a.dbid, a.instance_number, a.stat_name, SUM(a.value) DB_TIME_1 FROM DBA_HIST_SYS_TIME_MODEL a WHERE a.stat_name = 'DB time' AND a.instance_number = (select instance_number from v$instance) GROUP BY a.snap_id, a.dbid, a.instance_number, a.stat_name) a) c, dba_hist_snapshot b WHERE b.instance_number = (select instance_number from v$instance) AND c.snap_id = b.snap_id AND b.begin_interval_time > SYSDATE - 1 ORDER BY 1; PROMPTPROMPT 5.2、检查数据库连接情况col machine for a30SELECT username,program,machine,count(*) FROM v$session where username is not null group by username,program,machine order by 1,4 desc;PROMPT PROMPT 5.3、检查Oracle初始化文件中相关参数值PROMPT -initial_allocation：软限制，limit_value：硬限制PROMPT 若LIMIT_VALUE - MAX_UTILIZATION <= 5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过参数文件调整。SELECT resource_name,max_utilization,initial_allocation,limit_value FROM v$resource_limit order by 4,3;PROMPTPROMPT 六、检查数据库event和sqlPROMPTPROMPT 6.1、当前数据库的等待事件col wait_class for a15col event for a30SELECT inst_id,wait_class,event,count(*) FROM gv$session WHERE wait_class <> 'Idle' group by inst_id,wait_class,event order by inst_id,wait_class,event;PROMPTPROMPT 6.2、top5-eventPROMPT -TOTAL_WAITS：总等待的次数，AVERAGE_WAIT：每次等待的时间(单位：1s/100)col EVENT for a30SELECT * FROM (SELECT EVENT,TOTAL_WAITS,AVERAGE_WAIT,WAIT_CLASS# FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;PROMPTPROMPT 6.3、物理读top10-sqlcol sql_text for a65select * from(select sql_id, replace(substr(sql_text,1,300),chr(13),'') sql_text, disk_reads, executions, round(disk_reads/executions) "DiskReads/Exec"from v$sqlareawhere disk_reads > 0 and executions>0order by disk_reads desc)where rownum <= 10;PROMPTPROMPT 6.4、逻辑读top10-sqlselect * from(select sql_id, replace(substr(sql_text,1,300),chr(13),'') sql_text, buffer_gets, executions, round(buffer_gets/executions) "Gets/Exec"from v$sqlareawhere buffer_gets > 0 and executions>0order by buffer_gets desc)where rownum <= 10;PROMPTPROMPT 6.5、物理读top10-objectcol value for 999999999999col object_name for a30SELECT * FROM (SELECT owner,object_name,value FROM v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10;PROMPTPROMPT 6.6、热segment-top10col object_name for a30col OWNER for a20SELECT * FROM(SELECT ob.owner, ob.object_name,ob.object_type, sum(b.tch) TouchsFROM x$bh b , dba_objects obwhere b.obj = ob.data_object_id and b.ts# > 0group by ob.owner, ob.object_name, ob.object_typeorder by sum(tch) desc)where rownum <=10;PROMPTPROMPT 6.7、查看热点数据文件(从单块读取时间判断)PROMPT -CS：单块读的时间，READTIM：总的读时间，WRITETIM：总的写时间。单位都为毫秒COL file_name FOR a60SELECT * from (select t.file_name, t.tablespace_name, round(s.singleblkrdtim / s.singleblkrds /10, 3) AS CS, round(s.READTIM/10), round(s.WRITETIM/10) FROM v$filestat s, dba_data_files t WHERE s.file# = t.file_id order by cs desc) where rownum <= 10;PROMPTPROMPT 6.8、检查运行很久的SQLCOL USERNAME FOR A12COL OPNAME FOR A16COL PROGRESS FOR A8select b.username, a.sql_text, b.SID, b.SERIAL#, b.SOFAR, b.TOTALWORK, ROUND(b.SOFAR / b.TOTALWORK * 100, 2) "%_COMPLETE" from gv$sql a, gv$session_longops b where a.SQL_ID = b.SQL_ID and a.inst_id=b.inst_id and b.TOTALWORK <> 0 and b.SOFAR <> b.TOTALWORK; PROMPTPROMPT 6.9、检查执行计划最近一周发生突变的sqlwith A as (SELECTSQL.sql_id sql_id,SQL.PLAN_HASH_VALUE,min(to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')) Date_Time,sum(SQL.executions_delta) executions_delta,round(avg(SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta))) avg_lio,round(avg((SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta)),2) avg_cputime_s ,round(avg((SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta)),2) avg_etime_s,'HIS' as StatusFROMdba_hist_sqlstat SQL,dba_hist_sql_plan spl,dba_hist_snapshot sWHERESQL.dbid =(select dbid from v$database)and sql.sql_id=spl.sql_idand spl.object_owner not in ('SYS','SYSTEM','SYSMAN')and s.snap_id = SQL.snap_idand s.BEGIN_INTERVAL_TIME between trunc(sysdate-7) and trunc(sysdate)and SQL.executions_delta>100and SQL.PLAN_HASH_VALUE <> 0group by SQL.sql_id,SQL.PLAN_HASH_VALUEunion allselect B.sql_id,B.plan_hash_value,to_char(B.LAST_ACTIVE_TIME,'mm/dd/yy_hh24mi'),b.EXECUTIONS,b.BUFFER_GETS/b.EXECUTIONS,b.CPU_TIME/1000000/b.EXECUTIONS,b.ELAPSED_TIME/1000000/b.EXECUTIONS,'NOW' from v$sqlstats B where b.EXECUTIONS>100)select sql_id,PLAN_HASH_VALUE,Date_Time,executions_delta,round(avg_lio),trunc(avg_cputime_s,3),trunc(avg_etime_s,3),Status from A where sql_id in(select sql_id from(select sql_id,count(distinct PLAN_HASH_VALUE) from A group by sql_id having count(distinct PLAN_HASH_VALUE)>1)) order by sql_id,Date_Time;spool OFF
使用方法介绍：
1.上传脚本到数据库服务器或客户端
2.执行sqlplus / as sysdba
3.执行 @脚本名字
4.会自动生成一个oracle_xj_实例名_日期.txt的文件，下载到本地就可以看了