分享两个shell脚本，一键统计数据库临时表空间和阻塞锁信息

今天主要分享两个shell脚本，主要是查看数据库的临时表空间和阻塞锁信息。一起来看看吧~数据库连接脚本使用脚本settdb.shforDBlogindetailsregistry#!/bin/bashtmp_username=$SH_USERNAMEtmp_password=$SH_PASSWORDtmp_db_sid=$SH_DB_SID#check$1and$2shouldbemandatoryfrominputif[[-z$1]]||[[-z$2]];thenecho'*************************************************'echo'警告：需要用户名和密码！'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@_sqlCONplus_S$$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'已连接--\n'>>listdbecho'*************************************************'echo'InitalizeDBlogindetailsregistryOK!'echo'NowocanExecutionscript~'echo'************************************************'$SHELLelse##instisinaccessibleechoInstance:$SH_DB_SIDIsInvalidOrUserName/PassWordIsWrongecho'**************************************************'exitfidel_length=3tmp_txt=$(sed-n'$='listdb）回声'***********************************************'echo'*********'$SH_USERNAME'@'$SH_DB_SID'************'echo'*************************************************'curr_len=`cat$listfile|wc-l`if[$curr_len-gt$del_length];thenecho'ThereAreBelowSessionsStillAlive'echo'************************************************'已确定$((${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<=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_space_space_named.nameB).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_poolGROUPBY,tablespace_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.shshowlock.sh这里主要是查看阻塞锁信息，脚本内容如下：#!/bin/bashsqlplus-S$DB_CONN_STR@$SH_DB_SID<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