如何编写一键统计数据库临时表空间和阻塞lock信息的Shell脚本
这篇文章给大家介绍如何编写一键统计数据库临时表空间和阻塞lock信息的Shell脚本,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。
创新互联公司一直秉承“诚信做人,踏实做事”的原则,不欺瞒客户,是我们最起码的底线! 以服务为基础,以质量求生存,以技术求发展,成交一个客户多一个朋友!为您提供成都网站建设、网站设计、成都网页设计、小程序制作、成都网站开发、成都网站制作、成都软件开发、手机APP定制开发是成都本地专业的网站建设和网站设计公司,等你一起来见证!
今天主要分享一下两个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脚本就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
当前标题:如何编写一键统计数据库临时表空间和阻塞lock信息的Shell脚本
URL标题:http://pwwzsj.com/article/jicsgh.html