概述

今天主要分享一下两个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 <<EOF
clear buffer
set feed off
set verify off
set line 132
set pages 200

column bytes format 9999,999,999,999 head "Bytes Used"
column max_bytes format 9,999,999,999 head Quota
column default_tablespace format a20 head "Default Tablespace"
column tablespace_name for a25 
column username format a25 

prompt ******************************************************************************************************
prompt * General Details *
prompt ******************************************************************************************************
col profile format a10
col password_versions format a10
select username, default_tablespace, created ,profile, password_versions 
from dba_users 
 where username=upper('${NAME}')
/

prompt.
prompt ******************************************************************************************************
prompt * Objects General Info *
prompt ******************************************************************************************************
select object_type,status,count(*) obj_count 
 from dba_objects
 where owner=upper('$1') group by object_type,status order by obj_count desc
/
prompt.
prompt ******************************************************************************************************
prompt * Quotas *
prompt ******************************************************************************************************
select tablespace_name, 
 bytes, 
 decode( max_bytes,-1,'UNLIMITED',max_bytes) max_bytes
 from dba_ts_quotas where username=upper('${NAME}')
/
prompt.
prompt ******************************************************************************************************
prompt * Bytes Used 
prompt ******************************************************************************************************
col tablespace_name for a15 trunc
col MB head 'Size (Mb)' for 999,999,999

break on report 
compute sum of bytes on REPORT
/*
select 
		ts.tablespace_name tablespace_name,
		nvl(sum(seg.blocks*ts.block_size)/1024/1024,0) MB
from 
		dba_tablespaces ts,
		dba_segments seg,
		dba_users us
where
			-- du.username=upper('${NAME}') 
		us.username=upper('${NAME}') 	
	 	and	 seg.owner (+)= us.username 
		and ts.tablespace_name (+)= seg.TABLESPACE_NAME
group by ts.tablespace_name
order by ts.tablespace_name
*/

select 
		ts.name tablespace_name,
		nvl(sum(seg.blocks*ts.blocksize)/1024/1024,0) MB
from 
		sys.ts$ ts,
		sys.seg$ seg,
		sys.user$ us,
		dba_users du
where
			 du.username=upper('${NAME}') 
		and	 us.name (+)= du.username
	 	and	 seg.user# (+)= us.user# 
		and ts.ts# (+)= seg.ts#
group by ts.name
order by ts.name
/
prompt .
prompt ******************************************************************************************************
prompt * Grants/Roles *
prompt ******************************************************************************************************
set feed off verify off line 132 pages 200

col owner format a15
break on owner
prompt ********* OWNER ROLE *********** 
prompt ********************************
select d.owner,d.grantee role_name,r.PASSWORD_REQUIRED,s.admin_option,s.DEFAULT_ROLE
from dba_tab_privs d,dba_roles r,dba_role_privs s
where
 d.grantee=r.role
and d.grantee=s.grantee(+)
and d.owner=nvl(upper('$1'),' ')
group by d.grantee,d.owner,r.password_required,s.admin_option,s.DEFAULT_ROLE
order by d.owner;
column grantee format a20
column granted_role format a35
column admin_option heading admin format a10

prompt .
prompt ********** GRANTED ROLE ********
prompt ********************************
select d.grantee role_name
from dba_tab_privs d
where owner=upper('$1')
group by d.grantee
union
select granted_role
from dba_role_privs
 where grantee=upper('$1');
prompt .
prompt ******************************************************************************************************
prompt * Sys privileges *
prompt ******************************************************************************************************
set feed off verify off line 132 pages 200
column privilege format a25
column admin_option heading admin format a8

select privilege, 
 admin_option 
 from dba_sys_privs where grantee = upper('${NAME}')
/
!echo "******************************************************************************************************"
EOF
exit

输出：./showusers.sh 用户名

大家有什么需要统计的可以在下方留言，后面我也会整理相关脚本，感兴趣的朋友可以关注下