概述

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

数据库连接脚本

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

输出：./settdb.sh 用户名 用户密码

showprofile.sh

这里主要是查看profile的明细

脚本内容如下：

#!/bin/bash
echo "=======================================查看数据库用户$1的profile=========================================="
tab_owner=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID<<END
set head on
set pages 80
set linesize 150
col PROFILE format a30
col RESOURCE_NAME format a30
col RESOURCE_TYPE format a30
col LIMIT format a30
break on owner
select * 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" 
 echo
fi

输出：./showprofile.sh 用户名

showrowid.sh

脚本内容如下：

#!/bin/bash
echo "======================================根据rowid返回相关信息=========================================="
obj_owner=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID<<END
set head on
set pages 150
set linesize 150
col owner format a20
col object_name format a30
col object_Type format a15
select 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<<EOF
select 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_number
from dual;
prompt #################################
EOF
exit

输出：./showrowid.sh rowid

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