概述
今天主要分享一下两个shell脚本，主要是为了查看所有数据库用户及其表空间，统计某个指定用户的明细，下面一起来看看吧~
数据库连接脚本
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 用户名 用户密码
showusers.sh
脚本内容如下：
#!/bin/bashecho "========================================查看所有数据库用户及其默认表空间================================================="echo "set pages 70 lines 99 feedback offcol DEFAULT_TABLESPACE head 'Default TBS' for a15 trunccol TEMPORARY_TABLESPACE head 'TEMP TBS' for a15 trunccol MB head 'Size (Mb)' for 999,999,999col username format a30set linesize 150break on reportcompute sum of MB on reportselect USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,nvl(sum(seg.blocks*ts.blocksize)/1024/1024,0) MBfrom sys.ts$ ts,sys.seg$ seg,sys.user$ us,dba_users duwhere us.name (+)= du.username and seg.user# (+)= us.user# and ts.ts# (+)= seg.ts#group by USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATEDorder by MB desc,username,created/" | sqlplus -s $DB_CONN_STR@$SH_DB_SID
输出：./showusers.sh
showusers.sh
脚本内容如下：
#!/bin/bashecho "========================================查看所有数据库用户$1具体信息================================================="NAME=`echo $1|cut -d. -f1`if [ -z "$NAME" ] then echo -e "User must be provided: c"; read NAMEfisqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOFclear bufferset feed offset verify offset line 132set pages 200column bytes format 9999,999,999,999 head "Bytes Used"column max_bytes format 9,999,999,999 head Quotacolumn default_tablespace format a20 head "Default Tablespace"column tablespace_name for a25 column username format a25 prompt ******************************************************************************************************prompt * General Details *prompt ******************************************************************************************************col profile format a10col password_versions format a10select 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 trunccol MB head 'Size (Mb)' for 999,999,999break on report compute sum of bytes on REPORT/*select ts.tablespace_name tablespace_name,nvl(sum(seg.blocks*ts.block_size)/1024/1024,0) MBfrom dba_tablespaces ts,dba_segments seg,dba_users uswhere-- du.username=upper('${NAME}') us.username=upper('${NAME}') and seg.owner (+)= us.username and ts.tablespace_name (+)= seg.TABLESPACE_NAMEgroup by ts.tablespace_nameorder by ts.tablespace_name*/select ts.name tablespace_name,nvl(sum(seg.blocks*ts.blocksize)/1024/1024,0) MBfrom sys.ts$ ts,sys.seg$ seg,sys.user$ us,dba_users duwhere du.username=upper('${NAME}') and us.name (+)= du.username and seg.user# (+)= us.user# and ts.ts# (+)= seg.ts#group by ts.nameorder by ts.name/prompt .prompt ******************************************************************************************************prompt * Grants/Roles *prompt ******************************************************************************************************set feed off verify off line 132 pages 200col owner format a15break on ownerprompt ********* OWNER ROLE *********** prompt ********************************select d.owner,d.grantee role_name,r.PASSWORD_REQUIRED,s.admin_option,s.DEFAULT_ROLEfrom dba_tab_privs d,dba_roles r,dba_role_privs swhere d.grantee=r.roleand d.grantee=s.grantee(+)and d.owner=nvl(upper('$1'),' ')group by d.grantee,d.owner,r.password_required,s.admin_option,s.DEFAULT_ROLEorder by d.owner;column grantee format a20column granted_role format a35column admin_option heading admin format a10prompt .prompt ********** GRANTED ROLE ********prompt ********************************select d.grantee role_namefrom dba_tab_privs dwhere owner=upper('$1')group by d.granteeunionselect granted_rolefrom dba_role_privs where grantee=upper('$1');prompt .prompt ******************************************************************************************************prompt * Sys privileges *prompt ******************************************************************************************************set feed off verify off line 132 pages 200column privilege format a25column admin_option heading admin format a8select privilege, admin_option from dba_sys_privs where grantee = upper('${NAME}')/!echo "******************************************************************************************************"EOFexit
输出：./showusers.sh 用户名
大家有什么需要统计的可以在下方留言，后面我也会整理相关脚本，感兴趣的朋友可以关注下
