您的位置：首页 > 数码常识数码常识
shell脚本实现统计次数（shell操作数据库）
2025-05-11人已围观
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
输出：
http://sjzlt.cn/shuma/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
输出：http://sjzlt.cn/shuma/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
输出：http://sjzlt.cn/shuma/showproc.sh rfuser
上面就是小居数码小编今天给大家介绍的关于（shell操作数据库）的全部内容，希望可以帮助到你，想了解更多关于数码知识的问题，欢迎关注我们，并收藏，转发，分享。
94%的朋友还想知道的：
jenkins运行python脚本（jenkins定时执行py脚本）
按键精灵定时启动脚本的代码（脚本也能有定时功能吗）
按键精灵导出的脚本怎么使用（如何把脚本导入按键精灵）
shell脚本编程教程（Shell脚本编写）
155150
概述今天主要分享一下两个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
输出：
http://sjzlt.cn/shuma/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
输出：http://sjzlt.cn/shuma/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
输出：http://sjzlt.cn/shuma/showproc.sh rfuser
上面就是小居数码小编今天给大家介绍的关于（shell操作数据库）的全部内容，希望可以帮助到你，想了解更多关于数码知识的问题，欢迎关注我们，并收藏，转发，分享。
94%的朋友还想知道的：
jenkins运行python脚本（jenkins定时执行py脚本）
按键精灵定时启动脚本的代码（脚本也能有定时功能吗）
按键精灵导出的脚本怎么使用（如何把脚本导入按键精灵）
shell脚本编程教程（Shell脚本编写）
155150
很赞哦！ ()