shell脚本实现统计次数（shell操作数据库）

概述今天主要分享一下两个shell脚本，主要是为了查看数据库进程的相关信息和存储过程信息，

shell操作数据库

概述

数据库连接脚本

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`'-- 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 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 用户名 用户密码

showpid.sh

脚本内容：

#!/bin/bashecho "=================================================查看数据库进程spid：$1的相关信息====================================================="if [ -z "$1" ]; then  echo "no process has provided!"  exit 0fish_tmp_process=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<ENDset pagesize 0 feedback off verify off heading on echo off select addr from v$process where spid=$1;exit; END` if [ -z "$sh_tmp_process" ]; then  echo "no process exists or session is not from a DB account"  echo  echo "####### Process Information from OS level as below ########"  ps -ef|grep $1|grep -v "grep"|grep ora echo "##############################################"  exit 0 else  echo '*******************************************' echo "Process has found, pid: $1 , addr: $sh_tmp_process "  echo  echo "####### Process Information from OS level as below ########"  ps -ef|grep $1|grep -v grep|grep ora echo "##############################################"  sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOFcol machine format a20col terminal format a15col osuser format a15col process format a10col username format a15set linesize 1000 pagesize 500col type format a15col login_time format a20select sid,serial#,username,osuser ,machine,process,terminal,type,to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')login_time from v$sessionwhere paddr='$sh_tmp_process';prompt .col sql_id format a30col prev_sql_id format a30col sql_text format a60set linesize 150set pages 50select sql_id,sql_text from v$sql where sql_id in (select sql_id from v$session where paddr='$sh_tmp_process' and sql_id is not null ) and rownum<2;select sql_id prev_sql_id ,sql_text from v$sql where sql_id in (select prev_sql_id sql_id from v$session where paddr='$sh_tmp_process' ) and rownum<2;EOFfi

输出：./showpid.sh 数据库进程ID

这个一般要跟前面的会话脚本配合使用，主要是为了获得数据库进程更多明细。

showproc.sh

脚本内容：

一开始是想输出存储过程明细的，但是考虑到shell脚本看这些内容毕竟很痛苦，所以改成了去获得当前数据库所有存储过程，特定的存储过程还是用plsql看吧~

#!/bin/bashecho "=======================================查看数据库用户$1的存储过程信息=========================================="PROC_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<ENDset pagesize 100 feedback off verify off heading on echo offcol owner format a15col object_name format a30col object_type format a20col parallel format a10col interface format a10col aggregate format a10col pipelined format a10col deterministic format a10col authid format a10set linesize 1000select owner, object_name,object_id,object_type,aggregate,pipelined,parallel,interface,deterministic,authid from dba_procedureswhere owner=upper('$1') and object_type='PROCEDURE'/exit;END`if [ -z "$PROC_OWNER" ]; then echo "no object exists, please check again" exit 0else echo '*******************************************' echo " $PROC_OWNER " echo '*******************************************'fi#sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<EOF#prompt .#set long 99999#set pages 0#select text#from dba_source #where type='PROCEDURE' and owner=upper('$1')#order by line;#EOFexit

输出：./showproc.sh rfuser

上面就是小居数码小编今天给大家介绍的关于（shell操作数据库）的全部内容，希望可以帮助到你，想了解更多关于数码知识的问题，欢迎关注我们，并收藏，转发，分享。

94%的朋友还想知道的：

• jenkins运行python脚本（jenkins定时执行py脚本）
• 按键精灵定时启动脚本的代码（脚本也能有定时功能吗）
• 按键精灵导出的脚本怎么使用（如何把脚本导入按键精灵）
• shell脚本编程教程（Shell脚本编写）

（332）个朋友认为回复得到帮助。