概述

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

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