Shulou(Shulou.com)05/31 Report--

This article introduces how to write one-click statistics database temporary tablespaces and blocking lock information Shell script, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Today, we mainly share two shell scripts, mainly to view the temporary tablespaces and blocking lock information of the database. Let's take a look.

Database connection script

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 Needdred 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' [Amurz]'[Amurz] '`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 Murz]'[Amurz] '`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 Oklahs 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`cat [$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

Output:. / settdb.sh username user password

Showtsps.sh

#! / bin/bash echo "= = View database temporary tablespace =" 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_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

Output:. / showtsps.sh

Showlock.sh

The main purpose of this is to view the blocking lock information. The script content is as follows:

#! / bin/bash sqlplus-S $DB_CONN_STR@$SH_DB_SID

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.