資源描述：
《oracle巡檢腳本》由會(huì)員上傳分享，免費(fèi)在線(xiàn)閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。
1、1)數(shù)據(jù)庫(kù)session連接數(shù)selectcount(*)fromv$session;2)數(shù)據(jù)庫(kù)的并發(fā)數(shù)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)是否有大量長(zhǎng)事務(wù)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;臨時(shí)文件：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
