如何编写一键统计数据库临时表空间和阻塞lock信息的Shell脚本

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

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

数据库连接脚本

use script settdb.sh for DB login details registry

#!/bin/bash tmp_username=$SH_USERNAME tmp_password=$SH_PASSWORD tmp_db_sid=$SH_DB_SID  #check $1 and $2 should be mandatory from input if [[ -z $1 ]] || [[ -z $2 ]]; then echo '***********************************************' echo 'WARNING :UserName And PassWord Is Needed!' echo '***********************************************'  exit fi if [[ -z $3 ]] && [[ -z $ORACLE_SID ]];then echo '***********************************************' echo 'WARNING :There is Instance can be used !' echo '***********************************************' exit fi  SH_USERNAME=`echo "$1"|tr '[a-z]' '[A-Z]'` SH_PASSWORD=$2 echo '***********************************************'  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]'` fi  if [[ $SH_DB_SID = $tmp_db_sid ]] && [[ $SH_USERNAME = $tmp_username ]] && [[ $SH_PASSWORD = $tmp_password ]];then  echo 'Instance '$SH_DB_SID 'has been connected'  echo '***********************************************'  exit fi  export SH_USERNAME=$SH_USERNAME export SH_DB_SID=$SH_DB_SID export SH_PASSWORD=$SH_PASSWORD export DB_CONN_STR=$SH_USERNAME/$SH_PASSWORD #echo $DB_CONN_STR listfile=`pwd`/listdb Num=`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`'-- \n--'$SH_USERNAME@$SH_DB_SID 'has been connected --\n' >> 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  fi del_length=3 tmp_txt=$(sed -n '$=' listdb)  echo '***********************************************' echo '********* ' $SH_USERNAME'@'$SH_DB_SID '**********' echo '***********************************************' curr_len=`cat $listfile|wc -l` if [ $curr_len -gt $del_length ]; then echo ' There Are Below Sessions Still Alive ' echo '***********************************************' fi sed $((${tmp_txt}-${del_length}+1)),${tmp_txt}d $listfile | tee tmp_listfile mv tmp_listfile $listfile

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

showtsps.sh

#!/bin/bash echo "==================================================查看数据库临时表空间=================================================================" sqlplus -s $DB_CONN_STR@$SH_DB_SID <= 20 then ' ' else '*' end) alrt FROM 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) l WHERE 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 ALL select   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) alrt FROM 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) l WHERE 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 / prompt exit EOF

输出：./showtsps.sh

showlock.sh

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

#!/bin/bash sqlplus -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;  exit EOF

输出：./showlock.sh

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