这篇文章给大家介绍如何编写一键统计数据库临时表空间和阻塞lock信息的Shell脚本，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

今天主要分享一下两个shell脚本，主要是为了查看数据库的临时表空间和阻塞lock信息，下面一起来看看吧~

数据库连接脚本

use script settdb.sh for DB login details registry

#!/bin/bashtmp_username=$SH_USERNAMEtmp_password=$SH_PASSWORDtmp_db_sid=$SH_DB_SID#check$1and$2shouldbemandatoryfrominputif[[-z$1]]||[[-z$2]];thenecho'***********************************************'echo'WARNING:UserNameAndPassWordIsNeeded!'echo'***********************************************'exitfiif[[-z$3]]&&[[-z$ORACLE_SID]];thenecho'***********************************************'echo'WARNING:ThereisInstancecanbeused!'echo'***********************************************'exitfiSH_USERNAME=`echo"$1"|tr'[a-z]''[A-Z]'`SH_PASSWORD=$2echo'***********************************************'if[[-z$3]]thenSH_DB_SID=$ORACLE_SIDecho'UsingDefaultInstance:'$ORACLE_SIDecho.elseSH_DB_SID=`echo"$3"|tr'[a-z]''[A-Z]'`fiif[[$SH_DB_SID=$tmp_db_sid]]&&[[$SH_USERNAME=$tmp_username]]&&[[$SH_PASSWORD=$tmp_password]];thenecho'Instance'$SH_DB_SID'hasbeenconnected'echo'***********************************************'exitfiexportSH_USERNAME=$SH_USERNAMEexportSH_DB_SID=$SH_DB_SIDexportSH_PASSWORD=$SH_PASSWORDexportDB_CONN_STR=$SH_USERNAME/$SH_PASSWORD#echo$DB_CONN_STRlistfile=`pwd`/listdbNum=`echoshowuser|$ORACLE_HOME/bin/sqlplus-s$DB_CONN_STR@$SH_DB_SID|grep-i'USER'|wc-l`if[$Num-gt0]then##ok-instanceisupecho'Instance'$SH_DB_SID'hasbeenconnected'echo-e'--'`date`'--\n--'$SH_USERNAME@$SH_DB_SID'hasbeenconnected--\n'>>listdbecho'***********************************************'echo'InitalizeDBlogindetailsregistryOK!'echo'NowyoucanExecutionscript~'echo'***********************************************'$SHELLelse##instisinaccessibleechoInstance:$SH_DB_SIDIsInvalidOrUserName/PassWordIsWrongecho'***********************************************'exitfidel_length=3tmp_txt=$(sed-n'$='listdb)echo'***********************************************'echo'*********'$SH_USERNAME'@'$SH_DB_SID'**********'echo'***********************************************'curr_len=`cat$listfile|wc-l`if[$curr_len-gt$del_length];thenecho'ThereAreBelowSessionsStillAlive'echo'***********************************************'fised$((${tmp_txt}-${del_length}+1)),${tmp_txt}d$listfile|teetmp_listfilemvtmp_listfile$listfile

输出：./settdb.sh 用户名 用户密码

showtsps.sh

#!/bin/bashecho"==================================================查看数据库临时表空间================================================================="sqlplus-s$DB_CONN_STR@$SH_DB_SID<<EOFsetechooffheadingonunderlineon;columninst_numheading"InstNum"new_valueinst_numformat99999;columninst_nameheading"Instance"new_valueinst_nameformata12;columndb_nameheading"DBName"new_valuedb_nameformata12;columndbidheading"DBId"new_valuedbidformat9999999999justc;promptpromptCurrentInstanceprompt~~~~~~~~~~~~~~~~selectd.dbiddbid,d.namedb_name,i.instance_numberinst_num,i.instance_nameinst_namefromv\$databased,v\$instancei;settermonfeedbackofflines130pagesize999tabofftrimsoncolumnMBformat999,999,999heading"TotalMB"columnfreeformat9,999,999heading"FreeMB"columnusedformat99,999,999heading"UsedMB"columnLargestformat999,999heading"LrgstMB"columntablespace_nameformata20heading"Tablespace"columnstatusformata3truncatedcolumnmax_extentsformat99999999999heading"MaxExt"colextent_managementfora1trunchead"M"colallocation_typefora1trunchead"A"colExt_Sizefora4trunchead"Init"columnpfreeformata3truncheading"%Fr"breakonreportcomputesumofMBonreportcomputesumoffreeonreportcomputesumofusedonreportselectd.tablespace_name,decode(d.status,'ONLINE','OLN','READONLY','R/O',d.status)status,d.extent_management,decode(d.allocation_type,'USER','',d.allocation_type)allocation_type,(casewheninitial_extent<1048576thenlpad(round(initial_extent/1024,0),3)||'K'elselpad(round(initial_extent/1024/1024,0),3)||'M'end)Ext_Size,NVL(a.bytes/1024/1024,0)MB,NVL(f.bytes/1024/1024,0)free,(NVL(a.bytes/1024/1024,0)-NVL(f.bytes/1024/1024,0))used,NVL(l.large/1024/1024,0)largest,d.MAX_EXTENTS,lpad(round((f.bytes/a.bytes)*100,0),3)pfree,(casewhenround(f.bytes/a.bytes*100,0)>=20then''else'*'end)alrtFROMsys.dba_tablespacesd,(SELECTtablespace_name,SUM(bytes)bytesFROMdba_data_filesGROUPBYtablespace_name)a,(SELECTtablespace_name,SUM(bytes)bytesFROMdba_free_spaceGROUPBYtablespace_name)f,(SELECTtablespace_name,MAX(bytes)largeFROMdba_free_spaceGROUPBYtablespace_name)lWHEREd.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=f.tablespace_name(+)ANDd.tablespace_name=l.tablespace_name(+)ANDNOT(d.extent_managementLIKE'LOCAL'ANDd.contentsLIKE'TEMPORARY')UNIONALLselectd.tablespace_name,decode(d.status,'ONLINE','OLN','READONLY','R/O',d.status)status,d.extent_management,decode(d.allocation_type,'UNIFORM','U','SYSTEM','A','USER','',d.allocation_type)allocation_type,(casewheninitial_extent<1048576thenlpad(round(initial_extent/1024,0),3)||'K'elselpad(round(initial_extent/1024/1024,0),3)||'M'end)Ext_Size,NVL(a.bytes/1024/1024,0)MB,(NVL(a.bytes/1024/1024,0)-NVL(t.bytes/1024/1024,0))free,NVL(t.bytes/1024/1024,0)used,NVL(l.large/1024/1024,0)largest,d.MAX_EXTENTS,lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3)pfree,(casewhennvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100)>=20then''else'*'end)alrtFROMsys.dba_tablespacesd,(SELECTtablespace_name,SUM(bytes)bytesFROMdba_temp_filesGROUPBYtablespace_nameorderbytablespace_name)a,(SELECTtablespace_name,SUM(bytes_used)bytesFROMv\$temp_extent_poolGROUPBYtablespace_name)t,(SELECTtablespace_name,MAX(bytes_cached)largeFROMv\$temp_extent_poolGROUPBYtablespace_nameorderbytablespace_name)lWHEREd.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=t.tablespace_name(+)ANDd.tablespace_name=l.tablespace_name(+)ANDd.extent_managementLIKE'LOCAL'ANDd.contentsLIKE'TEMPORARY'ORDERby1/promptexitEOF

输出：./showtsps.sh

showlock.sh

这里主要是查看阻塞lock信息，脚本内容如下：

#!/bin/bashsqlplus-S$DB_CONN_STR@$SH_DB_SID<<EOFsetpages500setfeedbackoffsetverifyoffsetechoonsetlinesize1000colobject_nameformata25colosuserformata10colmachineformata12colprogramformata20--colobject_typeformata10colstateformata10colstatusformata10coloracle_usernameformata12colsid_serialformata12colsec_waitformat99999999collock_typeformata5colmode_heldformata10promptCurrentLocksprompt------------------------------------------------------------------------------------------------------selectses.sid||','||ses.serial#sid_serial,loc.oracle_username,object_name,--object_type,ses.LOGON_TIME,ses.SECONDS_IN_WAITsec_wait,ses.osuser,ses.machine,ses.program,ses.state,ses.status,decode(d.type,'MR','MediaRecovery','RT','RedoThread','UN','UserName','TX','Transaction','TM','DML','UL','PL/SQLUserLock','DX','DistribXaction','CF','ControlFile','IS','InstanceState','FS','FileSet','IR','InstanceRecovery','ST','DiskSpaceTransaction','TS','TempSegment','IV','LibraryCacheInvalidation','LS','LogStartorSwitch','RW','RowWait','SQ','SequenceNumber','TE','ExtendTable','TT','TempTable',d.type)lock_type,decode(d.lmode,0,'None',/*MonLockequivalent*/1,'Null',/*N*/2,'Row-S(SS)',/*L*/3,'Row-X(SX)',/*R*/4,'Share',/*S*/5,'S/Row-X(SSX)',/*C*/6,'Exclusive',/*X*/to_char(d.lmode))mode_heldfromv\$locked_objectloc,v\$sessionses,dba_objectsobj,v\$lockdwhereloc.object_id=obj.object_idandloc.session_id=ses.sidandobj.object_id=d.id1andses.sid=d.sidorderbyoracle_username,seconds_in_waitdesc;setheadoffSELECT'Therearealso'||count(*)||'transactionlocks'FROMv\$transaction_enqueue;prompt------------------------------------------------------------------------------------------------------setheadonsetlinesize1000pagesize1000col进程SIDfor99999trunccol锁类型formata10colSQL语句formata60col等待事件formata20col锁时间formata20col锁角色formata15col阻塞会话SIDformata30promptpromptBlockingSessionDetailsprompt------------------------------------------------------------------------------------------------------SELECTmm.inst_id"实例ID",mm.sid"进程SID",mm.TYPE"锁类型",mm.id1"事务号ID1",mm.id2"事务号ID2",LPAD(TRUNC(mm.ctime/60/60),3)||'Hour'||LPAD(TO_CHAR(TRUNC(mm.ctime/60)-TRUNC(mm.ctime/60/60)*60,'fm09'),2)||'Min'||LPAD(TO_CHAR(mm.ctime-TRUNC(mm.ctime/60)*60,'fm09'),2)||'Sec'"锁时间",CASEWHENmm.block=1ANDmm.lmode!=0THEN'holder'WHENmm.block=0ANDmm.request!=0THEN'waiter'ELSENULLEND"锁角色",CASEWHENee.blocking_sessionISNOTNULLTHEN'waitingforSID'||ee.blocking_sessionELSENULLEND"阻塞会话SID",dd.sql_text"SQL语句",cc.event"等待事件"FROMgv\$lockmm,gv\$sessionee,gv\$sqlareadd,gv\$session_waitccWHEREmm.sidIN(SELECTnn.sidFROM(SELECTtt.*,COUNT(1)OVER(PARTITIONBYtt.TYPE,tt.id1,tt.id2)cnt,MAX(tt.lmode)OVER(PARTITIONBYtt.TYPE,tt.id1,tt.id2)lmod_flag,MAX(tt.request)OVER(PARTITIONBYtt.TYPE,tt.id1,tt.id2)request_flagFROMgv\$locktt)nnWHEREnn.cnt>1ANDnn.lmod_flag!=0ANDnn.request_flag!=0)ANDmm.sid=ee.sid(+)ANDee.sql_id=dd.sql_id(+)ANDmm.sid=cc.sid(+)AND((mm.block=1ANDmm.lmode!=0)OR(mm.block=0ANDmm.request!=0))ORDERBYmm.TYPE,mm.id1,mm.id2,mm.lmodeDESC,mm.ctimeDESC;exitEOF

输出：./showlock.sh

关于如何编写一键统计数据库临时表空间和阻塞lock信息的Shell脚本就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。