概述

今天主要分享一下两个shell脚本，主要是为了查看数据库用户profile信息和rowid信息，下面一起来看看吧~

数据库连接脚本

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

showprofile.sh

这里主要是查看profile的明细

脚本内容如下：

#!/bin/bashecho "=======================================查看数据库用户$1的profile=========================================="tab_owner=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID<<ENDset head onset pages 80set linesize 150col PROFILE format a30col RESOURCE_NAME format a30col RESOURCE_TYPE format a30col LIMIT format a30break on ownerselect * from dba_profiles where profile in (select profile from dba_users where username=upper('$1'));exit; END` if [ -z "$tab_owner" ]; then  exit 0 else  echo '#################################' echo "$tab_owner"  echofi

输出：./showprofile.sh 用户名

showrowid.sh

脚本内容如下：

#!/bin/bashecho "======================================根据rowid返回相关信息=========================================="obj_owner=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID<<ENDset head onset pages 150set linesize 150col owner format a20col object_name format a30col object_Type format a15select owner,object_name,object_type,status,to_char(created,'yyyy-mm-dd')create_date from dba_objects where object_id=dbms_rowid.ROWID_OBJECT('$1')group by owner,object_name,object_type,status,to_char(created,'yyyy-mm-dd');exit; END` if [ -z "$obj_owner" ]; then  exit 0 else  echo '#################################' echo "$obj_owner"  echo '#################################'fi sqlplus -silent $DB_CONN_STR@$SH_DB_SID<<EOFselect dbms_rowid.ROWID_OBJECT('$1') object_id,dbms_rowid.ROWID_RELATIVE_FNO('$1') file_no,dbms_rowid.rowid_row_number('$1') row_no,dbms_rowid.rowid_block_number('$1') blk_numberfrom dual;prompt #################################EOFexit

输出：./showrowid.sh rowid

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