领导：如何用shell脚本统计Oracle数据库进程明细和存储过程信息

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

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

输出：

http://www.toutiao.com/a6760086278244401675/settdb.sh 用户名 用户密码

脚本内容：

#!/bin/bash

echo "=================================================查看数据库进程spid：$1的相关信息====================================================="

if [ -z "$1" ]; then

echo "no process has provided!"

exit 0

fi

sh_tmp_process=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <

set 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 <

col machine format a20

col terminal format a15

col osuser format a15

col process format a10

col username format a15

set linesize 1000 pagesize 500

col type format a15

col login_time format a20

select sid,serial#,username,osuser ,machine,process,terminal,type,to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')login_time from v\$session

where paddr='$sh_tmp_process';

prompt .

col sql_id format a30

col prev_sql_id format a30

col sql_text format a60

set linesize 150

set pages 50

select 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 rown

um<2;EOF

fi

输出：http://www.toutiao.com/a6760086278244401675/showpid.sh 数据库进程ID

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

脚本内容：

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

#!/bin/bash

echo "=======================================查看数据库用户$1的存储过程信息=========================================="

PROC_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <

set pagesize 100 feedback off verify off heading on echo off

col owner format a15

col object_name format a30

col object_type format a20

col parallel format a10

col interface format a10

col aggregate format a10

col pipelined format a10

col deterministic format a10

col authid format a10

set linesize 1000

select owner, object_name,object_id,object_type,aggregate,pipelined,parallel,interface,deterministic,authid from dba_procedures

where owner=upper('$1') and object_type='PROCEDURE'

/

exit;

END`

if [ -z "$PROC_OWNER" ]; then

echo "no object exists, please check again"

exit 0

else

echo '*******************************************'

echo " $PROC_OWNER "

echo '*******************************************'

fi

#sqlplus -silent $DB_CONN_STR@$SH_DB_SID <

#prompt .

#set long 99999

#set pages 0

#select text

#from dba_source

#where type='PROCEDURE' and owner=upper('$1')

#order by line;

#EOF

exit

输出：http://www.toutiao.com/a6760086278244401675/showproc.sh rfuser

前段时间头条bug导致很多代码都没显示，所以后面会整理脚本后一次性发出来，这段时间先写一下。

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！