領導：如何用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方面內容，感興趣的朋友可以關注下！