概述

今天主要分享一下两个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

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

showusers.sh

脚本内容如下：

#!/bin/bash
echo "========================================查看所有数据库用户及其默认表空间================================================="
echo "set pages 70 lines 99 feedback off
col DEFAULT_TABLESPACE head 'Default TBS' for a15 trunc
col TEMPORARY_TABLESPACE head 'TEMP TBS' for a15 trunc
col MB head 'Size (Mb)' for 999,999,999
col username format a30
set linesize 150

break on report
compute sum of MB on report

select 
		USERNAME,
		DEFAULT_TABLESPACE,
		TEMPORARY_TABLESPACE,
		CREATED,
		nvl(sum(seg.blocks*ts.blocksize)/1024/1024,0) MB
from 
		sys.ts$ ts,
		sys.seg$ seg,
		sys.user$ us,
		dba_users du
where
			 us.name (+)= du.username
	 	and	 seg.user# (+)= us.user# 
		and ts.ts# (+)= seg.ts#
group by USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED
order by MB desc,username,created
/
" | sqlplus -s $DB_CONN_STR@$SH_DB_SID

输出：./showusers.sh

showusers.sh

脚本内容如下：

#!/bin/bash
echo "========================================查看所有数据库用户$1具体信息================================================="
NAME=`echo $1|cut -d. -f1`
if [ -z "$NAME" ] 
then
 echo -e "User must be provided: c"; read NAME
fi

sqlplus -s $DB_CONN_STR@$SH_DB_SID <
输出：./showusers.sh 用户名
大家有什么需要统计的可以在下方留言，后面我也会整理相关脚本，感兴趣的朋友可以关注下