资源描述：
《oracle巡检脚本》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、1)数据库session连接数selectcount(*)fromv$session;2)数据库的并发数selectcount(*)fromv$sessionwherestatus='ACTIVE';3)是否存在死锁setlinesize200columnoracle_usernamefora16columnos_user_namefora12columnobject_namefora30SELECTl.xidusn,l.object_id,l.oracle_username,l.os_user_name,l.proces
2、s,l.session_id,s.serial#,l.locked_mode,o.object_nameFROMv$locked_objectl,dba_objectso,v$sessionswherel.object_id=o.object_idands.sid=l.session_id;selectt2.username
3、
4、' '
5、
6、t2.sid
7、
8、' '
9、
10、t2.serial#
11、
12、' '
13、
14、t2.logon_time
15、
16、' '
17、
18、t3.sql_text fromv$locked_objectt1,v$
19、sessiont2,v$sqltextt3 wheret1.session_id=t2.sid andt2.sql_address=t3.address orderbyt2.logon_time;4)是否有enqueue等待selecteq_type"lock",total_req#"gets",total_wait#"waits",cum_wait_timefromv$enqueue_statwheretotal_wait#>0;5)是否有大量长事务setlinesize200columnname
20、fora16columnusernamefora10selecta.name,b.xacts,c.sid,c.serial#,c.username,d.sql_textfromv$rollnamea,v$rollstatb,v$sessionc,v$sqltextd,v$transactionewherea.usn=b.usnandb.usn=e.XIDUSNandc.taddr=e.addrandc.sql_address=d.ADDRESSandc.sql_hashvalue=d.hash_valueorderbya.
21、name,c.sid,d.piece;6)表空间使用率setlinesize150columnfile_nameformata65columntablespace_nameformata20selectf.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,
22、2)used_g,round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2)used_percentfrom(selecttablespace_name,sum(bytes)sumbytesfromdba_free_spacegroupbytablespace_name)f,(selecttablespace_name,sum(bytes)sumbytesfromdba_data_filesgroupbytablespace_name)dwheref.tablespace_name=d.
23、tablespace_nameorderbyd.tablespace_name;临时文件：setlinesize200columnfile_nameformata55columntablespace_nameformata20selecta.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2)total_g,round(sum(nvl(b.bytes,0))/(1024*1024*1024),2)free_g,round((a.bytes/(1024*1
24、024*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
