oracle数据库巡检sql脚本
如何查询sga内各组件信息和pga大小SQL> conn / as sysdbaSQL> show parameter sga_max –查看sga的大小NAME TYPE V ALUE------------------------------------ ----------- ------------------------------sga_max_size big integer 1578706860SQL> show parameter sga_target –查看sga_target大小(只限10g以上版本)NAME TYPE V ALUE------------------------------------ ----------- ------------------------------sga_target big integer 584MSQL> show parameter db_cache_size –-查看db_cache大小NAME TYPE V ALUE------------------------------------ ----------- --------------------db_cache_size big integer 1048576000SQL> show parameter shared_pool_size –查看share_pool大小NAME TYPE V ALUE------------------------------------ ----------- ---------------------------shared_pool_size big integer 318767104SQL> show parameter pga—查看pga_aggregate_targetNAME TYPE V ALUE------------------------------------ ----------- ---------------pga_aggregate_target big integer 314572800查询数据库版本的sqlSQL> select VERSION from v$instance;VERSION-----------------9.2.0.1.0SQL> select name, log_mode from v$database; --查看数据库名，归档模式NAME LOG_MODE--------- ------------SKYOTA ARCHIVELOG –NOARCHIVELOG为非归档模式查看数据库字符集SQL> select value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';V ALUE-----------------------------------------------------ZHS16GBK查看redo log 信息查看日至组及成员SELECT group#,member FROM V$LOGFILE查看日志如成员大小SQL> select group# ,bytes ,members from v$log;GROUP# BYTES MEMBERS---------- ---------- ----------1 838860800 12 838860800 13 838860800 1近一个月内每天日志切换次数select to_char(first_time,'yyyy-mm-dd') day,count(*) times fromv$log_history where first_time> sysdate-30 group byto_char(first_time,'yyyy-mm-dd') ;查看监控当天高峰期日志切换情况–具体高峰时段根据项目特性修改sqlselect count(*) times from v$log_history where first_time betweento_date('2009-04-11 10:00:00','yyyy-mm-dd hh24:mi:ss') andto_date('2009-04-11 16:00:00','yyyy-mm-dd hh24:mi:ss');在oracle告警日志中（alertsid.log），也可以查看最近日志切换的情况表空间使用率：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)",d.max_autobytes "表空间扩展的最大尺寸(M)",TO_CHAR (ROUND ( (d.tot_grootte_mb - f.total_bytes)/ d.max_autobytes* 100,2),'990.99') "以使用占最大扩展比例"FROM (SELECT tablespace_name,ROUND (SUM (BYTES) / (1024 * 1024), 2) total_bytes, ROUND (MAX (BYTES) / (1024 * 1024), 2) max_bytesFROM SYS.dba_free_spaceGROUP BY tablespace_name) f,(SELECT dd.tablespace_name,ROUND (SUM (dd.BYTES) / (1024 * 1024),2) tot_grootte_mb,TRUNC ( SUM (DECODE (dd.maxbytes,0, dd.BYTES,dd.maxbytes))/ (1024 * 1024)) max_autobytesFROM SYS.dba_data_files ddGROUP BY dd.tablespace_name) dWHERE d.tablespace_name = f.tablespace_name;任务或job的运行情况selectschema_user,last_date,next_date,total_time,interval,failures,what from dba_jobs where schema_user='SKY' –以实际的业务用户为准查看系统当前的等待事件SELECT s.SID, w.event, ername, w.p1, w.p2, w.p3, s.logon_time, s.program,SYSDATEFROM v$session s, v$session_wait wWHERE s.SID = w.SIDAND w.event NOT LIKE '%SQL*Net%'AND w.event NOT LIKE '%rdbms%'AND w.event NOT LIKE '%timer%'AND w.event NOT LIKE '%jobq%'AND w.event NOT LIKE '%wakeup%';当前等待事件对应的sqlSELECT /*+ RULE*/s.SID, w.event, ername, t.hash_value, t.piece,t.sql_text, SYSDATEFROM v$session s, v$session_wait w, v$sqltext tWHERE s.SID = w.SIDAND s.sql_hash_value = t.hash_valueAND w.event IN('db file scattered read','db file sequential read','free buffer waits','enqueue','latch free','log file parallel write','log file sync','buffer busy waits','db file parallel write','db file single write','direct path read','direct path write','free buffer inspected','free buffer waits','library cache load lock','library cache lock','library cache pin','log buffer space','log file single write','log file switch (checkpoint incomplete)','log file switch (archiving needed)','log file switch (clearing log file)','log file switch completion','undo segment extension')ORDER BY s.SID, t.piece;当前主要全表扫描语句SELECT b.sql_text, b.executions, b.sorts, b.disk_reads,b.buffer_gets, a.hash_value, a.object_owner, a.object_name,SYSDATEFROM v$sql_plan a, v$sql bWHERE a.address = b.addressAND a.hash_value = b.hash_valueAND a.child_number = b.child_numberAND a.operation = 'TABLE ACCESS'AND a.options = 'FULL'AND a.object_owner NOT IN('ODM','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','SYS','SYSTEM');单次执行物理读多前20个语句SELECT * FROM(SELECTround(disk_reads/decode(EXECUTIONS,0,1,executions),2) bit,PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_textFROM v$sqlareaORDER BY bit DESC)WHERE ROWNUM<21 ;物理多总数多前10个sqlSELECT * FROM(SELECT PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_textFROM v$sqlareaORDER BY disk_reads DESC)WHERE ROWNUM<11 ;单次逻辑读多的前10个sqlSELECT *FROM (SELECT s.sid,b.spid,s.sql_hash_value,q.sql_text,q.executions,q.buffer_gets,ROUND(q.buffer_gets / q.executions) AS buffer_per_exec,ROUND(q.elapsed_time / q.executions) AS cpu_time_per_exec,q.cpu_time,q.elapsed_time,q.disk_reads,q.rows_processedFROM v$session s, v$process b, v$sql qWHERE s.sql_hash_value = q.hash_valueAND s.paddr = b.addr-- AND s.status = 'ACTIVE'AND s.TYPE = 'USER'AND q.buffer_gets > 0AND q.executions > 0ORDER BY buffer_per_exec DESC)WHERE ROWNUM <= 10;逻辑读多的前10个sqlSELECT *FROM (SELECT s.sid,b.spid,s.sql_hash_value,q.sql_text,q.executions,q.buffer_gets,q.cpu_time,q.elapsed_time,q.disk_reads,q.rows_processedFROM v$session s, v$process b, v$sql qWHERE s.sql_hash_value = q.hash_valueAND s.paddr = b.addr-- AND s.status = 'ACTIVE'AND s.TYPE = 'USER'AND q.buffer_gets > 0AND q.executions > 0ORDER BY buffer_gets DESC)WHERE ROWNUM <= 10;用户对象在非自定义表空间中存储的情况（owner='SKY'以实际业务用户为准）select segment_name,segment_type,tablespace_name,bytes fromdba_segments where owner='SKY' AND TABLESPACE_NAMEIN('SYSTEM','USERS','SYSAUX')系统中最大的二十个表大小及高水位select segment_name, "SIZE(M)",TABLESPACE_NAME from(select segment_name,bytes/1024/1024"SIZE(M)",TABLESPACE_NAME from user_segments ORDER BY BYTES DESC) where rownum<=20。
数据库巡检
1 日常巡检1.1 数据库巡检为了保证oracle数据库稳定，高效的运行，每个季度初需要对oracle数据库进行健康检查。
以确定数据库是否存在故障及性能问题。
对于异常状况，上报，进一步诊断、分析，及时解决。
巡检工作包括以下细则：●ALERT文件(alertSID.log)是否出现错误信息●top10等待事件●数据库大小●表空间使用情况●内存配置●三个Top10 SQL●内存命中率●归档方式及备份情况1.1.1 巡检脚本1.1.1.1 AlertSID.log文件位置：1.1.1.2 归档方式及备份情况（1）查看是否为归档方式：（2）说明该数据库备份情况，是否有备份策略。
1.1.1.3 top10等待事件：◆不同的版本，事件的多少不同✧Oracle9iOracle10g1.1.1.4 数据库大小：1.1.1.5 表空间使用情况：1.1.2 Top10segment◆查找系统数据量最大的10个段1.1.2.1 内存配置✧oracle9i：✧Oracle10g:1.1.2.2 三个Top10 SQL1.1.2.3 命中率1.1.2.4 死锁死锁查询：SELECT /*+ rule */ername,decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuserFROM v$session s, v$lock l, dba_objects oWHERE l.sid = s.sidAND l.id1 = o.object_id(+)AND ername is NOT NULL解锁：杀死该session：alter system kill session 'sid,serial#'。
Oracle数据库巡检报告
检查结果:正常
查看有无“ORA-”，Error”，“Failed”等出错信息。根据错误信息进行分析并解决
2.1.11检查当前crontab任务
（1）任务清单
$ crontab -l
（2）Oracle Job是否有失败
SQL> select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
检查结果:正常
如果disk/(memoty+row)的比例过高，则需要调整
2.3.7检查日志缓冲区
SQL> select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');
order By Percent;
检查结果：正常
2.2.2
SQL> archive log list;
检查结果：正常
2.2.3
SQL> col name for a55
SQL>select file#,ts#,status,name from v$datafile;
检查结果：正常
2.2.4
#df -h
检查结果：正常
2.3
2.3.1负载情况（Load Profile）
生成awr报告
SQL>@?/rdbms/admin/awrrpt
检查结果：正常
如果DBtime远小于elapse说明数据库比较空闲
oracle性能检测sql语句
oracle性能检测sql语句1. 监控事例的等待select event,sum(decode(wait_Time,0,0,1)) "Prev",sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"from v$session_Waitgroup by event order by 4;2. 回滚段的争⽤情况select name, waits, gets, waits/gets "Ratio"from v$rollstat a, v$rollname bwhere n = n;3. 监控表空间的 I/O ⽐例select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbwfrom v$filestat f, dba_data_files dfwhere f.file# = df.file_idorder by df.tablespace_name;4. 监控⽂件系统的 I/O ⽐例select substr(a.file#,1,2) "#", substr(,1,30) "Name",a.status, a.bytes,b.phyrds, b.phywrtsfrom v$datafile a, v$filestat bwhere a.file# = b.file#;5.在某个⽤户下找所有的索引select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = user_indexes.table_nameorder by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;6. 监控 SGA 的命中率select a.value + b.value"logical_reads", c.value"phys_reads",round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"from v$sysstat a, v$sysstat b, v$sysstat cwhere a.statistic# = 38 and b.statistic# = 39and c.statistic# = 40;7. 监控 SGA 中字典缓冲区的命中率select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"from v$rowcachewhere gets+getmisses <>0group by parameter, gets, getmisses;8. 监控 SGA 中共享缓存区的命中率，应该⼩于1%select sum(pins) "Total Pins", sum(reloads) "Total Reloads",sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;9. 显⽰所有数据库对象的类别和⼤⼩select count(name) num_instances ,type ,sum(source_size) source_size ,sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_requiredfrom dba_object_sizegroup by type order by 2;10. 监控 SGA 中重做⽇志缓存区的命中率，应该⼩于1%SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');11. 监控内存和硬盘的排序⽐率，最好使它⼩于 .10，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');12. 监控当前数据库谁在运⾏什么SQL语句SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;13. 监控字典缓冲区SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 后者除以前者,此⽐率⼩于1%,接近0%为好。
Oracle数据库教程 —— sqlserver 巡检脚本
Oracle数据库教程—— sqlserver 巡检脚本--1.查看数据库版本信息select @@version--2.查看所有数据库名称及大小exec sp_helpdb--3.查看数据库所在机器的操作系统参数exec master..xp_msver--4.查看数据库启动的参数exec sp_configure--5.查看数据库启动时间select convert(varchar(30),login_time,120)from master..sysprocesses where spid=1--6.查看数据库服务器名select 'Server Name:'+ltrim(@@servername)--7.查看数据库实例名select 'Instance:'+ltrim(@@servicename)--8.数据库的磁盘空间呢使用信息exec sp_spaceused--9.日志文件大小及使用情况dbcc sqlperf(logspace)--10.表的磁盘空间使用信息exec sp_spaceused 'tablename'--11.获取磁盘读写情况select@@total_read [读取磁盘次数],@@total_write [写入磁盘次数],@@total_errors [磁盘写入错误数],getdate() [当前时间]--12.获取I/O工作情况select @@io_busy [自上次启动的I/O操作毫秒数],@@timeticks [每个时钟周期对应的微秒数],@@io_busy*@@timeticks [I/O操作毫秒数],getdate() [当前时间]--13.查看CPU活动及工作情况select@@cpu_busy [自上次启动CPU的工作时间毫秒数],@@timeticks [每个时钟周期对应的微秒数],@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],getdate() [当前时间]--14.检查锁与等待exec sp_lock--15.检测死锁和阻塞declare @spid int,@bl int,@intTransactionCountOnEntry int,@intRowcount int,@intCountProperties int,@intCounter intcreate table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 print @@ERRORinsert into #tmp_lock_who(spid,bl) select 0 ,blockedfrom (select * from sysprocesses where blocked>0 ) awhere not exists(select * from (select * from sysprocesseswhere blocked>0 ) bwhere a.blocked=spid)union select spid,blocked from sysprocesses where blocked>0IF @@ERROR<>0 print @@ERROR-- 找到临时表的记录数select @intCountProperties = Count(*),@intCounter = 1from #tmp_lock_whoIF @@ERROR<>0 print @@ERRORif @intCountProperties=0select '现在没有阻塞和死锁信息' as message-- 循环开始while @intCounter <= @intCountPropertiesbegin-- 取第一条记录select @spid = spid,@bl = blfrom #tmp_lock_who where Id = @intCounterbeginif @spid =0select '引起数据库死锁的是: '+ CAST(@bl AS V ARCHAR(10))+ '进程号,其执行的SQL语法如下'elseselect '进程号SPID：'+ CAST(@spid AS V ARCHAR(10))+ '被'+ '进程号SPID：'+ CAST(@bl AS V ARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl )end-- 循环指针下移set @intCounter = @intCounter + 1end/--16.用户和进程信息exec sp_whoexec sp_who2--17.活动用户和进程的信息exec sp_who 'active'--19.查看所有数据库用户登录信息exec sp_helplogins--20.查看所有数据库用户所属的角色信息exec sp_helpsrvrolemember--21.查看链接服务器exec sp_helplinkedsrvlogin--22.查看远端数据库用户登录信息exec sp_helpremotelogin--23.获取网络数据包统计信息select@@pack_received [输入数据包数量],@@pack_sent [输出数据包数量],@@packet_errors [错误包数量],getdate() [当前时间]--24.检查数据库中的所有对象的分配和机构完整性是否存在错误dbcc checkdb--25.查询文件组和文件selectdf.[name],df.physical_name,df.[size],df.growth,f.[name][filegroup],f.is_defaultfrom sys.database_files df join sys.filegroups fon df.data_space_id = f.data_space_id--26.查看数据库中所有表的条数select as tablename ,a.rowcnt as datacountfrom sysindexes a ,sysobjects bwhere a.id = b.idand a.indid < 2and objectproperty(b.id, 'IsMSShipped') = 0--27.得到最耗时的前10条T-SQL语句;with maco as(select top 10plan_handle,sum(total_worker_time) as total_worker_time ,sum(execution_count) as execution_count ,count(1) as sql_countfrom sys.dm_exec_query_stats group by plan_handleorder by sum(total_worker_time) desc)select t.text ,a.total_worker_time ,a.execution_count ,a.sql_countfrom maco across apply sys.dm_exec_sql_text(plan_handle) t--28. 查看SQL Server的实际内存占用select * from sysperfinfo where counter_name like '%Memory%'--29.显示所有数据库的日志空间信息dbcc sqlperf(logspace)--30.收缩数据库dbcc shrinkdatabase(databaseName)更多文章可见：公司官网：。
oracle巡检 手册
Oracle巡检手册第一部分数据库状态监控首先检查oracle的log，在sqlplus中：show parameter background_dump_dest;select * from v$diag_info;可以得到日志路径1:检查oracle 监听lsnrtcl statusPs –ef|grep ora2:检查oracle初始化参数Select * from v$parameter;3:检查oracle实例状态Select instance_name,version,status,database_status from v$instance;select inst_id,instance_name,host_name,VERSION,TO_CHAR(startup_time,'yyyy-mm-dd hh24:mi:ss')startup_time,status,archiver,database_status FROM gv$instance;4:检查后台进程状态：select name,Description From v$BGPROCESS Where Paddr<>'00'5:查看系统全局区SGA信息select * from v$sga;6: 查看SGA各部分占用内存情况：select * from v$sgastat;select request_misses,request_failures from v$shared_pool_reserved;比较好的状态：REQUEST_MISSES REQUEST_FAILURES为0或者接近0REQUEST_MISSES REQUEST_FAILURES-------------- ----------------007：查看系统SCN号select (select dbms_flashback.get_system_change_number from dual)scn,current_scn,scn_to_timestamp(current_scn)from v$database;8：检查数据库状态：select name,log_mode,open_mode,platform_name from v$database;select inst_id,dbid,name,to_char(created,'yyyy-mm-dd hh24:mi:ss')created,log_mode,to_char(version_time,'yyyy-mm-ddhh24:mi:ss')version_time,open_mode from gv$database;第二部分：数据库空间监控检查表空间使用率select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percentfrom (select tablespace_name, sum(bytes) totalfrom dba_free_spacegroup by tablespace_name) A,(select tablespace_name, sum(bytes) totalfrom dba_data_filesgroup by tablespace_name) Bwhere A.tablespace_name = B.tablespace_name;检查system表空间内的内容select distinct (owner)from dba_tableswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM'unionselect distinct (owner)from dba_indexeswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM';输出：no rows selected分析：如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。
Oracle数据库巡检
序号检查内容正常值(参考) 影响因素1 --高速缓存的命中率select round((1 -(physical.value- direct.value- lobs.value) /logical.value) * 100,2) || '%' "高速缓存的命中率"from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical where = 'physical reads'and = 'physical reads direct'and = 'physical reads direct (lob)'and = 'session logical reads';90%-100%(可能略低于90%在数据库繁忙运行期间)1. Buffer 命中率受OracleSGA中的data blockbuffers参数的设置影响2. 跟Oracle buffer Pool的使用方法有关3. 把经常使用的小表cache在内存中4. 调优SQL语句，以养活少访问的数据量db_cache_size?2 --库缓存的命中率select round(sum(pins -reloads) / sum(pins) * 100, 2) || '%' "库缓存的命中率"from v$librarycache;95%-100%1. Library命中率受OracleSGA中的shared pool参数设置影响2. 跟应用软件的开发有密切的关系，特别是共享SQL的使用3 --闩命中率select round((1 -sum(misses +immediate_misses) / sum(gets + immediate_gets)) * 100,2) || '%' "闩命中率"from v$latch;99%-100%1. 应用程序SQL是否使用绑定变量2. Shared_pool_size参数的设置4 --内存排序率select round((1- disk.value/(disk.value+ memory.value)) *100, 2) || '%' "内存排序率"from v$sysstat disk, v$sysstat memorywhere = 'sorts (disk)'and = 'sorts 99%-100%1. 数据库参数sort_area_size或pga_aggregate_target的大小2. 应用程序的SQL语句的写法(memory)';5 --缓冲区未等待率select round((1- busy.value/tol.value) * 100, 2) || '%' "缓冲区未等待率"from(select sum(count) valuefrom v$waitstatwhere class in('data block', 'segment header', 'undo header', 'undo block')) busy, (select value from v$sysstat where name= 'session logical reads') tol;99%-100%1. db_block_buffers或db_cache_size等参数2. 增加表的Freelist参数3. 使用AutomaticSegment StorgeManagement（ASSM）来创建表空间4. 优化程序使用的SQL语句6 --redo缓冲区未等待率select round((1 - waits.value/ redos.value) * 100, 2) || '%'"redo缓冲区未等待率"from v$sysstat waits, v$sysstat redoswhere = 'redo log space requests'and = 'redo entries';99%-100%1. Log_buffer_size参数设置过小2. 归档的速度太慢3. 联机日志文件太小4. 联机日志文件放在缓慢的磁盘设备上7 --SQL语句执行和分析的比例select round((1- hard.value/total.value) * 100, 2) || '%'"SQL语句执行和分析的比例"from v$sysstat hard, v$sysstat totalwhere = 'parse count (hard)'and = 'parse count (total)';越接近100%越好1. Share_pool_size参数的大小2. 最重要的影响因素是应用程是否使用了绑定变量8 --析的CPU的时间和分析完成CPU时间对比select round((1 - cpu.value /total.value) * 100, 2) || '%'"cpu分析和完成比"from v$sysstat cpu, v$sysstat totalwhere = 'parse time cpu'越接近100%越好1. 如果这个比例很低，说明分析过程中CPU等待了其它的资源and = 'parse time elapsed';9 --非分析的过程中CPU对比select round((1 - parse.value/ total.value) * 100, 2) || '%'"非分析的过程中CPU对比"from v$sysstat parse, v$sysstat totalwhere = 'parse time cpu'and = 'CPU used by this session';越接近100%越好1. 如果这个比例很低，说明CPU用在分析SQL语句上面消耗了很多CPU时间，可能是没有用绑定变量10 --等待rollback segment的header比率select name,waits,gets,round(waits / gets * 100, 2) || '%'"等待rollbacksegment的header比"from v$rollstat a, v$rollname bwhere n = n;rollback segment等待率比率越小越好1. 回滚段竟争情况受回滚段size的设置影响2. 跟应用软件的有关，特别是long runnig timetransaction的使用11 --Tablespace的I/O比例select df.tablespace_name,sum(f.phyrds),sum(f.phyblkrd),sum(f.phywrts),sum(f.phyblkwrt)from v$filestat f, dba_data_files dfwhere f.file# = df.file_id group by df.tablespace_name order by df.tablespace_name;Tablespace I/O越小越好1. Tablespace的I/O情况受db_block_size参数的设置影响2. 跟数据文件的磁盘分布有密切关系12 --Datafile 的I/O比例select ,sum(f.phyrds),sum(f.phyblkrd),sum(f.phywrts),sum(f.phyblkwrt)from v$filestat f, v$datafile dfwhere f.file# = df.file# group by Datafile I/O越小越好1. Datafile的I/O情况受db_block_size参数的设置影响2. 跟数据文件的磁盘分布有密切关系order by ;13 --重做日志缓存区命中率select name,gets,misses,immediate_gets,immediate_misses,100- round(decode(gets, 0, 0,misses / gets * 100), 2) || '%'ratio1,100 -round(decode(immediate_gets + immediate_misses,0,0,immediate_misses / (immediate_gets + immediate_misses) * 100),2) || '%' ratio2from v$latchwhere name in('redo allocation', 'redo copy');重做日志缓存区的命中率越大越好，应大于90%1. 受log_buffer_size设置影响2. 跟应用软件的有关，特别是共享SQL的使用14 --碎片程度select tablespace_name,round(sqrt(max(blocks) /sum(blocks)) *(100/ sqrt(sqrt(count(blocks)))),2) || '%' FSFIfrom dba_free_spacegroup by tablespace_name order by tablespace_name;FSFI越大越好，应大于30%1. 碎片情况受db_block_size,segment_size的设置影响。
oracle巡检脚本
1) 数据库session连接数select count(*) from v$session;2) 数据库的并发数select count(*) from v$session where status='ACTIVE';3) 是否存在死锁set linesize 200column oracle_username for a16column os_user_name for a12column object_name for a30SELECT l.xidusn,l.object_id,l.oracle_username,l.os_user_name,l.proc ess,l.session_id,s.serial#, l.locked_mode,o.object_name FROM v$locked_object l,dba_objects o,v$session s where l.object_id = o.object_id and s.sid =l.session_id;selectername||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_textfrom v$locked_object t1,v$session t2,v$sqltext t3where t1.session_id=t2.sidand t2.sql_address=t3.addressorder by t2.logon_time;4) 是否有enqueue等待select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat wheretotal_wait#>0;5) 是否有大量长事务set linesize 200column name for a16column username for a10select,b.xacts,c.sid,c.serial#,ername,d.sql_tex tfrom v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction ewhere n=nand n=e.XIDUSNand c.taddr=e.addrand c.sql_address=d.ADDRESSand c.sql_hashvalue=d.hash_valueorder by ,c.sid,d.piece;6)表空间使用率set linesize 150column file_name format a65column tablespace_name format a20select f.tablespace_nametablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,round(f.sumbytes/1024/1024/1024,2) free_g,round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percentfrom (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,(select tablespace_name,sum(bytes) sumbytes fromdba_data_files group by tablespace_name) dwhere f.tablespace_name= d.tablespace_nameorder by d.tablespace_name;临时文件：set linesize 200column file_name format a55column tablespace_name format a20selecta.tablespace_name,a.file_name,round(a.bytes/(1024*1 024*1024),2) total_g,round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g, round((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g, round(((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_gfrom dba_temp_files a,dba_free_space bwhere a.file_id = b.file_id(+)group by a.tablespace_name,a.file_name,a.bytesorder by a.tablespace_name;selecta.tablespace_name,a.file_name,round(a.bytes/(1024*1 024*1024),2) total_g,round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g, round((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g, round(((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_gfrom dba_temp_files a,dba_free_space bwhere a.file_id = b.file_id(+)group by a.tablespace_name,a.file_name,a.bytesorder by a.tablespace_name;归档的生成频率:set linesize 120column begin_time for a26column end_time for a26select a.recid,to_char(a.first_time,'yyyy-mm-ddhh24:mi:ss') begin_time,b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round((b.first_time - a.first_time)*24*60,2) minutes from v$log_history a,v$log_history bwhere b.recid = a.recid+1;sql读磁盘的频率:select ername,b.disk_reads,b.executions,round((b.disk_reads/decode(b.executions,0,1,b.execu tions)),2) disk_read_ratio,b.sql_textfrom dba_users a,v$sqlarea bwhere er_id = b.parsing_user_idand disk_reads > 5000;Datafile I/O:col tbs for a12;col name for a46;select c.tablespace_nametbs,,a.phyblkrd+a.phyblkwrtTotal,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrtfrom v$filestat a,v$datafile b,dba_data_files c where b.file# = a.file#and b.file# = c.file_idorder by tablespace_name,a.file#;Disk I/Oselect substr(,1,13)disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds, 0,1,a.phyblkrd))/100) avg_rd_time,((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_timefrom v$filestat a,v$datafile b,dba_data_files c where b.file# = a.file#and b.file# = c.file_idorder by disk,c.tablespace_name,a.file#;select ername,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_textfrom dba_users a,v$sqlarea bwhere er_id = b.parsing_user_idand b.buffer_gets > 5000000;col index_name for a16;col table_name for a18;col column_name for a18;selectindex_name,table_name,column_name,column_position from user_ind_columnswhere table_name = '&tbs';大事务：select sid,serial#,to_char(start_time,'yyyy-mm-ddhh24:mi:ss')start_time,sofar,totalwork,(sofar/decode(totalwork, 0,1,totalwork))*100 ratio,message fromv$session_longopswhere message like '%RMAN%';select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss')start_time,sofar,totalwork,(sofar/decode(totalwork, 0,1,totalwork))*100 ratio,message fromv$session_longopswhere sofar <> totalwork;where (sofar/totalwork)*100 < 100;索引检查：set linesize 200;column index_name for a15;column index_type for a10;column table_name for a15;column tablespace_name for a16;selectindex_name,index_type,table_name,tablespace_name from user_indexeswhere table_name ='&t';set linesize 200;column index_name for a26;column table_name for a26;column column_name for a22;column column_position for 999;column tablespace_name for a16;selecttable_name,index_name,column_name,column_position from user_ind_columns where table_name = '&tab'; selecttable_name,index_name,column_name,column_position from user_ind_columns where index_name = '&ind'; selecttable_name,index_name,index_type,status,TABLESPACE_ NAME from user_indexes where table_name = '&tab'; selecttable_name,index_name,index_type,status,TABLESPACE_ NAME from user_indexes where index_name = '&ind'; set linesize 200;column index_name for a20;column table_name for a20;select index_name,index_type,table_name,partitioned from user_indexes where index_name = '&ind';等待事件：set linesize 200column username for a12column program for a30column event for a28column p1text for a15column p1 for 999,999,999,999,999select ername,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait swwhere s.sid=sw.sid and s.status='ACTIVE'order by sw.p1;select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_waitorder by event;where event = 'buffer busy waits';selectowner,segment_name,segment_type,file_id,block_id from dba_extentswhere file_id = &P1 and &P2 between block_id and block_id + blocks -1;column event for a35;column p1text for a40;select sid,event,p1,p1text from v$session_wait order by event;查询相关SQL：set linesize 200set pagesize 1000column username for a8column program for a36selects.sid,s.serial#,ername,s.program,st.sql_text from v$session s,v$sqltext stwhere s.sql_hashvalue=st.hash_value ands.status='ACTIVE'order by s.sid,st.piece;select pid,spid from v$process p,v$session s where s.sid=&sid and p.addr = s.paddr;selects.sid,s.serial#,ername,s.program,st.sql_text from v$session s,v$sqltext st,v$process ps where s.sql_hashvalue=st.hash_valueand ps.spid=&sid and s.paddr=ps.addrorder by s.sid,st.piece;select sql_text from v$sqltextwhere hash_value in (select sql_hash_value fromv$sessionwhere paddr in (select addr from v$processwhere spid=&sid))order by piece;select sql_text from v$sqltextwhere address in (select sql_address from v$session where paddr in (select addr from v$processwhere spid=&sid))order by piece;select sql_text from v$sqltextwhere hash_value in (select sql_hash_value fromv$session where sid=&sid)order by piece;select sql_text from v$sqltextwhere address in (select sql_address from v$session where sid=&sid)order by piece;selectps.addr,ps.pid,ps.spid,ername,ps.program,s.sid ,ername,s.programfrom v$process ps,v$session swhere ps.spid=&pidand s.paddr=ps.addr;selects.sid,s.serial#,ername,s.program,st.sql_text from v$session s,v$sqltext st,v$process pswhere s.sql_hashvalue=st.hash_valueand ps.spid='29863' and s.paddr=ps.addrorder by s.sid,st.piece;column username for a12column program for a20select ername,s.program,s.osuser,statusfrom v$session swhere s.status='ACTIVE';query undotbs used percent:set linesize 300;selecttablespace_name,segment_name,status,count(*),round( sum(bytes)/1024/1024,2) used_M from dba_undo_extents group by tablespace_name,segment_name,status;set linesize 300column username for a10;column program for a25;selectername,s.program,status,p.spid,st.sql_text from v$session s,v$process p,v$sqltext st wheres.status='ACTIVE' and p.addr=s.paddr andst.hashvalue=s.sql_hash_value order bys.sid,st.piece;selectsnap_id,dbid,instance_number,to_char(snap_time,'yyy y-mm-dd hh24:mi:ss') snap_time from stats$snapshot order by INSTANCE_NUMBER,SNAP_ID,SNAP_TIME;set linesize 120;column what form a30;select job,log_user,what,instance from dba_jobs; set linesize 120;column owner for a12;column segment_name for a24;column segment_type for a18;selectowner,segment_name,segment_type,file_id,block_idfrom dba_extentswhere file_id=&file and &block between block_id and block_id + blocks - 1;select file_id,file_name from dba_data_files where file_id = &file_id;ANALYZE TABLE ICS_ODS_CUST_ICS_CURpartition(ICS_ODS_CUST_ICS_CUR_PART_1)VALIDATE STRUCTURE CASCADE;ANALYZE TABLE ODSDATA.&object VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;analyze index SYS_C00311764 validate structure cascade;column owner for a12;column segment_name for a26;column segment_type for a16;column tablespace_name for a20;column bytes for 999,999,999,999;selectowner,segment_name,segment_type,tablespace_name,byt es,blocks,buffer_pool from dba_segmentswhere segment_name='&seg'order by bytes desc;selectsegment_name,segment_type,tablespace_name,partition _name,bytes from user_segmentswhere segment_name='ODSV_REC_FILE'and segment_name in (select distinct table_name from user_part_col_statistics wheretable_name='ODSV_REC_FILE')order by bytes desc;col object_name for a26;select object_name,object_type,status,temporary from user_objectswhere object_name = '&o';set linesize 180break on hash_value skip 1 dupcol child_number format 999 heading 'CHILD'col operation format a82col cost format 999999col Kbytes format 999999col object format a25select hash_value,child_number,lpad(' ', 2 * depth) || operation || ' ' || options ||decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,object_name object,cost,cardinality,round(bytes / 1024) kbytesfrom v$sql_planwhere hashvalue=&hash_value/*in(select a.sql_hash_valuefrom v$session a, v$session_wait bwhere a.sid = b.sid and b.event = 'db file scattered read')*/order by hash_value, child_number, id;。
ORACLE 数据库巡检报告脚本
**************************************************************** # made by brain zhang# products made by brain zhang is competitive products**************************************************************** SET MARKUP HTML ON SPOOL ON pre off entmap offSET ECHO OFFSET TERMOUT OFFSET TRIMOUT OFFset feedback offset heading onset linesize 200set pagesize 10000col tablespace_name format a15col total_space format a10col free_space format a10col used_space format a10col used_rate format 99.99column dbid new_value spool_dbidcolumn inst_num new_value spool_inst_numselect dbid from v$database where rownum = 1;select instance_number as inst_num from v$instance where rownum = 1;column spoolfile_name new_value spoolfileselect 'spool_'||(select name from v$database where rownum=1) ||'_'|| (select instance_name from v$instance where rownum=1) ||'_'||to_char(sysdate,'yy-mm-dd_hh24.mi')||'_static' as spoolfile_name from dual;spool &&spoolfile..htmlset line 140 pages 9000;col action_time for a30;col action for a10;col namespace for a15;col version for a20;col comments for a30;prompt system info check!/sbin/ip addr!hostname!df -h!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep ora-!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep err!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep failprompt 1.database version and patch checkselect action_time,action, namespace,version,comments from dba_registry_history;prompt 2.database id checkselect dbid from v$database;prompt 3. database force logging、SUPPLEMENTAL_LOG_DATA_MIN、FLASHBACK_ON checkcol FORCE_LOGGING for a3;col SUPPLEMENTAL_LOG_DATA_MIN for a10;col SUPPLEMENTAL_LOG_DATA_PK for a3;col SUPPLEMENTAL_LOG_DATA_UI for a3;col SUPPLEMENTAL_LOG_DATA_FK for a3col SUPPLEMENTAL_LOG_DATA_ALL for a3;col FLASHBACK_ON for a15;selectFORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEME NTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_F K,SUPPLEMENTAL_LOG_DATA_ALL,FLASHBACK_ONfrom v$database;prompt 4. database SESSIONS_CURRENT、SESSIONS_HIGHWATERselect INST_ID,SESSIONS_CURRENT,SESSIONS_HIGHWATER from gv$license;prompt 5. database profilescol limit for a30;select * from dba_profiles order by 1;prompt 6. database languageselect userenv('language') from dual;prompt 7. database instance statuscol INSTANCE_NAME for a20;col host_name for a20;selectinst_id,instance_number,instance_name,host_name,statusfrom gv$instance;prompt 8. database sum sizeselect sum(bytes)/1024/1024/1024 as GB from dba_segments;prompt 9. database controlfileCOL NAME FOR A50;select * from v$controlfile;prompt 10. database logfileselect THREAD#,GROUP#,SEQUENCE#, BYTES/1024/1024,status,FIRST_TIME from v$log;col member for a50;select * from v$logfile;prompt 11. database archivearchive log list;prompt 12. database tablespace checkcol file_name for a50;col tablespace_name for a20;selectfile_id,tablespace_name,file_name,bytes/1024/1024,status,AUT OEXTENSIBLE,MAXBYTES/1024/1024from dba_data_files;SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfileSELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FREE_SPACE,0) "FREE_SPACE(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);prompt 13. database backup!sh rman_back.shspool offexit;#check rman back scriptscat >rman_back.sh<<EOFrman target / <<EOFlist backup of database summary; quitEOF。
精心整理了一套Oracle日常巡检脚本，速速收藏！
精心整理了一套Oracle日常巡检脚本，速速收藏！展开全文SQL专栏SQL数据库基础知识汇总SQL数据库高级知识汇总前言0.登录数据库sqlplus / nologconn /as sysdba(或者conn 账号/密码)1. 检查数据库基本状况包含：检查Oracle实例状态，检查Oracle 服务进程，检查Oracle监听进程，共三个部分。
1.1. 检查Oracle实例状态设置打印输出一行的字符数，这里设置成500。
set linesize 500;select instance_name,host_name,startup_time,status,database_statusfrom v$instance;其中v$instance是实例状态视图；“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。
1.2. 检查Oracle在线日志状态selectgroup#,status,type,memberfrom v$logfile;其中v$logfile是描述重做日志信息的视图，上面的输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。
注：“STATUS”显示为空表示正常。
1.3. 检查Oracle表空间的状态selecttablespace_name,statusfrom dba_tablespaces;dba_tablespaces是所有表空间的描述信息，上面的输出结果中STATUS应该都为ONLINE。
1.4. 检查Oracle所有数据文件状态select name,status from v$datafile;其中v$datafile是从oracle的控制文件中获得的数据文件的信息的，上面的输出结果中“STATUS”应该都为“ONLINE”。
oracle自动化巡检脚本
#!/bin/bash## NAME# report_oracle_inspection.sh 2013-12-18## DESCRIPTION# collecting the DB info## NOTES# sh report_oracle_inspection.sh## MODIFIED (yyyy-mm-dd)# zhangheli 2010-08-11 初步改成用shell执行# zhangheli 2011-07-02 增加temp表空间情况查询echo 'Instance Health Data'echo '================================================'echo 'The current database is $ORACLE_SID'echo 'The current running processes for $ORACLE_SID are'echo '================================================'ps -ef|grep $ORACLE_SIDsqlplus -S /nolog <<EOFconnect / as sysdbaset feedback offset heading offselect '00.instance information' from dual;select '================================================' from dual; set linesize 1000set pagesize 1000set heading onselect * from v\$instance;set heading offselect '01:database created date and archive type' from dual;select '================================================' from dual; set heading onSelect Created, Log_Mode, Log_Mode From V\$Database;set heading offselect '1.ulimit oracle' from dual;select '================================================' from dual; !ulimit -aselect '3.installed production option' from dual;select '================================================' from dual; set linesize 1000set pagesize 1000set heading onselect * from v\$option;set heading offselect 'ed production option' from dual;select '================================================' from dual; set linesize 1000set pagesize 1000col COMP_NAME for a40set heading onselect COMP_ID, COMP_NAME, VERSION,STATUS from dba_registry;set heading offselect '5.spfile' from dual;select '================================================' from dual; show parameter spfileset heading offselect '6.not default parameter' from dual;select '================================================' from dual; col name for a40col value for a40set heading onselect name,value from v\$parameter where isdefault='FALSE';set heading offselect '7.control file' from dual;select '================================================' from dual; show parameter control_filesset heading offselect '8.backup control file' from dual;select '================================================' from dual; alter database backup controlfile to trace;set heading offselect '9.log file' from dual;select '================================================' from dual; set linesize 1000set pagesize 1000select group#,thread#,bytes/1024/1024 size_MB , members, archived,status from v\$Log;set heading offselect '10.log file' from dual;col MEMBER for a40select '================================================' from dual;set heading onselect * From v\$logfile order by 1;set heading offselect '11.Archive log' from dual;select '================================================' from dual;Archive log listselect '12.data file' from dual;select '================================================' from dual;set heading onselect count(*),sum(bytes)/1024/1024/1024 ||'G' max_G from v\$datafile;SELECT trunc(sum(sum_m-sum_free_m)/1024,2)||'G' used_GFROM (SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files where tablespace_name not like 'UNDO%' GROUP BY tablespace_name) df, (SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_free_mFROM dba_free_space GROUP BY tablespace_name ) fswhere df.tablespace_name=fs.tablespace_name;set heading offselect '13.data file location' from dual;select '================================================' from dual;set heading onselect t1.TABLESPACE_NAME,t1.FILE_ID, t1.bytes/1024/1024SIZE_MB,t1.AUTOEXTENSIBLE AUT,t2.status,t1.FILE_NAMEfrom dba_data_files t1,v\$datafile t2where t1.file_id=t2.file#;set heading offselect '13-1.temp data file' from dual;select '================================================' from dual;set heading onselect FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024byte_MB,status,AUTOEXTENSIBLE from sys.dba_temp_files;set heading offselect '13-2.temp tablespace' from dual;select '================================================' from dual;set heading oncol file_name for a30col byte_MB for a20col cached_MB for a20SELECT d.file_name, v.status, TO_CHAR((d.bytes / 1024 / 1024), '99999990.000') byte_MB,TO_CHAR(NVL(t.bytes_cached, 0) / 1024 / 1024, '99999990.000') cached_MB,d.autoextensible, d.increment_by, d.maxblocksFROM sys.dba_temp_files d, v\$temp_extent_pool t, v\$tempfile vWHERE (t.file_id (+)= d.file_id) AND (d.tablespace_name = 'TEMP') AND (d.file_id = v.file#);set heading offselect '14.system tablespace' from dual;select '================================================' from dual;set heading onselect owner,segment_type,segment_name from dba_segments where owner notin('SYS','SYSTEM','MDSYS','ORDSYS','OUTLN','WMSYS') andtablespace_name='SYSTEM' order by 1;exitEOFora_version=`sqlplus -S '/ as sysdba' <<EOFset head offselect version from v\\\$instance;exit;EOF`echo $ora_versionif [ `echo $ora_version|awk -F"." '{print $1}'` -ne 8 ]thensqlplus -S /nolog <<EOFconn / as sysdbaset linesize 1000set pagesize 1000set heading offselect '15.tablespace fragmentation and free' from dual;select '================================================' from dual;col TABLESPACE_NAME for a30col FREE_PCT for a20set heading onSELECT df.TABLESPACE_NAME,FILES, extent_management ,sum_m asTOTAL_SIZE,--sum(largest) as "MAXFREE_MB",sum_free_m as "FREE_MB",to_char(100*sum_free_m/sum_m, '999.99') ASFREE_PCT--,sum(blocks) as "FREE_EXTENTS"FROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name) df,(SELECT tablespace_name,--max(bytes)/1024/1024 largest,sum(bytes)/1024/1024 AS sum_free_m --,count(blocks) as blocksFROM dba_free_space GROUP BY tablespace_name ) fs,(selecttablespace_name,extent_management from dba_tablespaces) tswhere df.tablespace_name=fs.tablespace_name andfs.tablespace_name=ts.tablespace_name;exit;EOFelsesqlplus -S /nolog <<EOFconn / as sysdbaset linesize 1000set pagesize 1000select '16.tablespace fragmentation and free (8i)' from dual;select '================================================' from dual;col TABLESPACE_NAME for a30col FREE_PCT for a20set heading onSELECT df.TABLESPACE_NAME,FILES, sum_m as TOTAL_SIZE,--sum(largest) as "MAXFREE_MB",sum_free_m as "FREE_MB",to_char(100*sum_free_m/sum_m, '999.99') ASFREE_PCT--,sum(blocks) as "FREE_EXTENTS"FROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name) df,(SELECT tablespace_name,--max(bytes)/1024/1024 largest,sum(bytes)/1024/1024 AS sum_free_m --,count(blocks) as blocksFROM dba_free_space GROUP BY tablespace_name ) fs,(select tablespace_name from dba_tablespaces) tswhere df.tablespace_name=fs.tablespace_name andfs.tablespace_name=ts.tablespace_name;exit;EOFfisqlplus -S /nolog <<EOFconn / as sysdbaset linesize 1000set pagesize 1000set heading offselect '17.object list' from dual;select '================================================' from dual;col OBJECT_TYPE for a20set heading onselect owner,replace(object_type,' ','_') as OBJECT_TYPE,count(*) from dba_objects whereowner not in ('SYS','SYSTEM') group by owner,object_type order by owner,object_type;set heading offselect '18.invalid objects' from dual;select '================================================' from dual;col OBJECT_NAME for a40col OBJECT_TYPE for a20set heading onselect OWNER,OBJECT_NAME,replace(OBJECT_TYPE,' ','_') asOBJECT_TYPE,STATUS,TIMESTAMP from dba_objects where status='INVALID';set heading offselect '19.dblinks' from dual;select '================================================' from dual;col DB_LINK for a40col OWNER for a10col HOST for a20set heading onselect * from dba_db_links;set heading offselect '20.indexes' from dual;select '================================================' from dual;set heading onselect * From dba_indexes where BLEVEL>4;set heading offselect '21.dba role' from dual;select '================================================' from dual;set heading onselect grantee,granted_role from dba_role_privs where granted_role='DBA';set heading offselect '22.sysdba role' from dual;select '================================================' from dual;set heading onSELECT * FROM v\$pwfile_users order by username;set head offselect '2-performance' from dual;select'============================================================================= ===================' from dual;select '2-1.buffer cache hit ratio:(Higher than 80% is ok, high value does not alwasy mean good performance)' from dual;select '================================================' from dual;set head onselect (1 - (sum(decode(name, 'physical reads', value, 0)) /(sum(decode(name, 'db block gets', value, 0)) +sum(decode(name, 'consistent gets', value, 0))))) * 100"Hit Ratio" from v\$sysstat;set head offselect '2-2.data dictionary hit ratio:should >98%' from dual;select '================================================' from dual;set head onselect (1 - (sum(getmisses) / sum(gets))) * 100 "Hit Ratio"from v\$rowcache;set head offselect '2-3.library cache hit ratio:(Should be kept over 90%, otherwise there mighe be too much reparse)' from dual;select '================================================' from dual;set head onselect sum(pins) / (sum(pins) + sum(reloads)) * 100 "Hit Ratio"from v\$librarycache;set head offselect '2-4.menory sort ratio:should >98%' from dual;select '================================================' from dual;set head onselect a.value "Disk Sorts",b.value "Memory Sorts",round((100 * b.value) /decode((a.value + b.value), 0, 1, (a.value + b.value)),2) "Pct Memory Sorts"from v\$sysstat a, v\$sysstat bwhere = 'sorts (disk)'and = 'sorts (memory)';set head offselect '2-5.memory top 10 sql read ratio:should <5%' from dual;select '================================================' from dual;set head onselect sum(pct_bufgets)from (select rank() over(order by buffer_gets desc) as rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over(), '999.99') pct_bufgetsfrom v\$sqlarea)where rank_bufgets < 11;set heading offselect '' from dual;select '2-6.Top 10 Wait Event (Time unit:Hundreths of a second, IO operations should be common wait event)' from dual;select '================================================' from dual;set heading oncolumn event format a30select * from (select event,total_waits,time_waited, average_wait fromv\$system_event whereevent not like 'SQL*Net%' and event not like '%ipc%' order by total_waits desc) whererownum<11;set head offselect '2-7.memory top 10 sql' from dual;select '================================================' from dual;set head onset serveroutput on size 1000000declaretop10 number;text1 varchar2(4000);x number;len1 number;cursor c1 isselect buffer_gets, substr(sql_text, 1, 4000)from v\$sqlareaorder by buffer_gets desc;begindbms_output.put_line('------------' || ' ' || '-------------------');open c1;for i in 1 .. 10 loopfetch c1 into top10, text1;dbms_output.put_line('------------top sql No.' ||i||'-------------------');dbms_output.put_line(rpad(to_char(top10), 9) );len1 := length(text1);x := 1;while len1 > x - 1 loopdbms_output.put_line(' ' || substr(text1, x, 65));x := x + 66;end loop;end loop;end;/set head offselect '2-8.IO information' from dual;select '================================================' from dual;set head onSelect phyrds,phywrts, from v\$datafile d,v\$filestat f wheref.file#=d.file# order by ;set head offselect '2-9.full table scan' from dual;select '================================================' from dual;set head onSelect name,value value1 from v\$sysstat where name like '%table scan%';set head offselect '3-1.sys and system security' from dual;select '================================================' from dual; select username "User(s) with Default Password!",ACCOUNT_STATUSfrom dba_userswhere password in('E066D214D5421CCC', -- dbsnmp'24ABAB8B06281B4C', -- ctxsys'72979A94BAD2AF80', -- mdsys'C252E8FA117AF049', -- odm'A7A32CD03D3CE8D5', -- odm_mtr'88A2B2C183431F00', -- ordplugins'7EFA02EC7EA6B86F', -- ordsys'4A3BA55E08595C81', -- outln'F894844C34402B67', -- scott'3F9FBD883D787341', -- wk_proxy'79DF7A1BD138CF11', -- wk_sys'7C9BA362F8314299', -- wmsys'88D8364765FCE6AF', -- xdb'F9DA8977092B7B81', -- tracesvr'9300C0977D7DC75E', -- oas_public'A97282CE3D94E29E', -- websys'AC9700FD3F1410EB', -- lbacsys'E7B5D92911C831E1', -- rman'AC98877DE1297365', -- perfstat'66F4EF5650C20355', -- exfsys'84B8CBCA4D477FA3', -- si_informtn_schema'D4C5016086B2DC6A', -- sys'D4DF7931AB130E37') -- system;exitEOFcd $ORACLE_HOME/network/admin/echo '3-2.listener configure'echo 'listener.ora================================================'cat listener*.orasleep 2;echo 'sqlnet.ora================================================='cat sqlnet*.orasleep 2;echo 'tnsnames.ora================================================'cat tnsnames*.orasleep 2;echo '3-3.controlfile dump============================================' ora_dump=`sqlplus -S '/ as sysdba' <<EOFset head offselect valuefrom v\\\$parameterwhere name='user_dump_dest';exit;EOF`cd $ora_dumpls -lt|head -n 2|tail -n 1|awk '{print $9}'|xargs catsleep 2;echo '3-4.Alert Log ORA- WarningError============================================'ora_background_dump=`sqlplus -S '/ as sysdba' <<EOFset head offselect valuefrom v\\\$parameterwhere name='background_dump_dest';exit;EOF`cd $ora_background_dumptail -10000 alert_$ORACLE_SID.log|grep ORA-sleep 2;echo '3-5.Alert Log size============================================'ls -l alert_$ORACLE_SID.logecho '3-6.listener.log size============================================' lsnrctl status|grep listener.log|awk '{print $4}'|xargs ls -lecho '3-7.crontab info============================================' crontab -lecho '3-8.Alert Log tail 20000nums============================================'tail -20000 alert_$ORACLE_SID.logSYSTEM=`uname -s`export SYSTEMecho '4.machine information============================================'if [ $SYSTEM = "Linux" ] ; thenecho "----------------host name----------------"hostnameecho ""echo "----------------id----------------"idecho ""echo '--- Current uptime,users and load averages ---'uptimeecho ""echo "----------------CPU number----------------"cat /proc/cpuinfosleep 1;echo ""echo "----------------memory info----------------"cat /proc/meminfosleep 1;echo ""echo "----------------disk info----------------"df -ksleep 1;echo ""echo "----------------kernel parameter----------------" cat /etc/sysctl.confsleep 1;echo ""echo "----------------os lever----------------"lsb_release -asleep 1;echo ""echo "----------------product type----------------" dmidecode |grep Productsleep 1;echo ""echo "----------------CPU memory usage----------------" vmstat 5 5sleep 1;echo ""echo "----------------top info----------------"top -d 1 -n 20sleep 5;top -d 1 -n 20sleep 5;top -d 1 -n 20sleep 5;top -d 1 -n 20sleep 5;top -d 1 -n 20elif [ $SYSTEM = "SunOS" ] ; thenecho "----------------host name----------------" hostnameecho ""echo "----------------id----------------"idecho "----------------CPU,memory number----------------" /usr/platform/sun4u/sbin/prtdiag -vecho "----------------os lever----------------"cat /etc/releaseecho "----------------Kernel parameter----------------" /usr/sbin/sysdef |grep SHM/usr/sbin/sysdef |grep SEMcat /etc/systemecho "----------------disk info----------------"df -kifconfig -asleep 1;elif [ $SYSTEM = "AIX" ] ; thenecho "----------------host name----------------" hostnameecho ""echo "----------------id----------------"idecho ""echo "----------------machine plat----------------" uname -Mecho ""echo "----------------CPU,memory number----------------" prtconfsleep 2;echo ""echo "----------------disk info----------------"df -kecho ""echo "----------------os lever----------------"oslevel -recho ""echo "----------------kernel parameter----------------" lsattr -El sys0echo ""echo "----------------HACMP----------------"lslpp -l |grep clusterecho ""echo "----------------network parameter----------------" no -aecho ""echo "----------------CPU memory usage----------------" vmstat 5 5sleep 1;echo ""echo "----------------IP info----------------"ifconfig -asleep 1;echo ""echo "----------------view cluster----------------"lssrc -g clustersleep 1;echo ""lsvgsleep 1;echo ""elif [ $SYSTEM = "HP-UX" ] ; thenecho "----------------host name----------------" hostnameecho ""echo "----------------id----------------"idecho ""echo "----------------machine plat----------------" modelecho ""echo "----------------CPU,memory number----------------" machinfosleep 2;echo ""echo "----------------disk info----------------"bdfecho ""echo "----------------os lever----------------"oslevel -recho ""echo "----------------HACMP----------------"lslpp -l |grep clusterecho ""echo "----------------network parameter----------------" no -aecho ""echo "----------------CPU memory usage----------------" vmstat 5 5sleep 1;sar -du 5 5echo ""echo "----------------IP info----------------"ifconfig -aelseecho "What?"fi。