shell sqlplus执行sql文_详解如何用shell脚本一键统计Oracle数据库阻塞lock信息

概述

今天主要分享一下两个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 $1 and $2 should be mandatory from inputif [[ -z $1 ]] || [[ -z $2 ]]; thenecho '***********************************************'echo 'WARNING :UserName And PassWord Is Needed!'echo '***********************************************'exitfiif [[ -z $3 ]] && [[ -z $ORACLE_SID ]];thenecho '***********************************************'echo 'WARNING :There is Instance can be used !'echo '***********************************************'exitfiSH_USERNAME=`echo "$1"|tr '[a-z]' '[A-Z]'`SH_PASSWORD=$2echo '***********************************************'if [[ -z $3 ]]then SH_DB_SID=$ORACLE_SID echo 'Using Default Instance :'$ORACLE_SID echo .else SH_DB_SID=`echo "$3"|tr '[a-z]' '[A-Z]'`fiif [[ $SH_DB_SID = $tmp_db_sid ]] && [[ $SH_USERNAME = $tmp_username ]] && [[ $SH_PASSWORD = $tmp_password ]];then echo 'Instance '$SH_DB_SID 'has been connected' echo '***********************************************' exitfiexport SH_USERNAME=$SH_USERNAMEexport SH_DB_SID=$SH_DB_SIDexport SH_PASSWORD=$SH_PASSWORDexport DB_CONN_STR=$SH_USERNAME/$SH_PASSWORD#echo $DB_CONN_STRlistfile=`pwd`/listdbNum=`echo show user | $ORACLE_HOME/bin/sqlplus -s $DB_CONN_STR@$SH_DB_SID| grep -i 'USER ' | wc -l`if [ $Num -gt 0 ] then ## ok - instance is up echo 'Instance '$SH_DB_SID 'has been connected' echo -e '--' `date`'-- --'$SH_USERNAME@$SH_DB_SID 'has been connected --' >> listdb echo '***********************************************' echo 'Initalize DB login details registry OK!' echo 'Now you can Execution script~' echo '***********************************************' $SHELL  else ## inst is inaccessible  echo Instance: $SH_DB_SID Is Invalid Or UserName/PassWord Is Wrong  echo '***********************************************' exit fidel_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 ' There Are Below Sessions Still Alive 'echo '***********************************************'fised $((${tmp_txt}-${del_length}+1)),${tmp_txt}d $listfile | tee tmp_listfilemv tmp_listfile $listfile

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

---

showtsps.sh

#!/bin/bashecho "==================================================查看数据库临时表空间================================================================="sqlplus -s $DB_CONN_STR@$SH_DB_SID <= 20 then ' ' else '*' end) alrtFROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f, (SELECT tablespace_name, MAX(bytes) large FROM dba_free_space GROUP BY tablespace_name) lWHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = l.tablespace_name(+) AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')UNION ALLselect  d.tablespace_name,  decode(d.status,  'ONLINE', 'OLN', 'READ ONLY', 'R/O', d.status) status, d.extent_management,  decode(d.allocation_type, 'UNIFORM','U', 'SYSTEM','A', 'USER','', d.allocation_type) allocation_type, (case  when initial_extent < 1048576  then lpad(round(initial_extent/1024,0),3)||'K'  else lpad(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, (case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100) >= 20 then ' ' else '*' end) alrtFROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_temp_files GROUP BY tablespace_name order by tablespace_name) a, (SELECT tablespace_name, SUM(bytes_used ) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) t, (SELECT tablespace_name, MAX(bytes_cached) large FROM v$temp_extent_pool GROUP BY tablespace_name order by tablespace_name) lWHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = l.tablespace_name(+) AND d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY' ORDER by 1/promptexitEOF

输出：./showtsps.sh

---

showlock.sh

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

#!/bin/bashsqlplus -S $DB_CONN_STR@$SH_DB_SID < 1  AND nn.lmod_flag != 0  AND nn.request_flag != 0)  AND mm.sid = ee.sid (+)  AND ee.sql_id = dd.sql_id (+)  AND mm.sid = cc.sid (+)  AND ((mm.block = 1  AND mm.lmode != 0)  OR (mm.block = 0  AND mm.request != 0))  ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC,  mm.ctime DESC;exitEOF

输出：./showlock.sh

---

大家还有什么需要做统计的可以在下方留言哦，后面我会一起加进来。后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下