########### USER INPUTS ###########
sys_username="system"
sys_password="
schema_name_string="
########### USER INPUTS ###########
filename="username.txt"
SUCCESS=0
FAILURE=1
IMPORT_EXECUTABLE=sqlplus
function init
{
echo "Initialising..."
IMPORT_EXECUTABLE_LOCATION=$( which ${IMPORT_EXECUTABLE} | grep "no ${IMPORT_EXECUTABLE}" )
if [[ -n "${IMPORT_EXECUTABLE_LOCATION}" ]] ; then
echo "Unable to execute import script as the import executable [${IMPORT_EXECUTABLE}] is not on the path"
exit ${FAILURE}
fi
}
function getUserName {
sqlplus -s /nolog << EOF
CONNECT $sys_username/$sys_password
SPOOL $filename
SET LINESIZE 100
SET PAGESIZE 50
select username from all_users where username like '%$schema_name_string%';
SPOOL OFF
exit;
EOF
}
function readUserNamesandCheckDiskUsage {
awk '{ print $0}' $filename | grep -v -E '(^USERNAME|^SQL|^[0-9]|^$|^-)' | (
IFS='
' read -d '' -a arr
for linename in ${arr[@]}
do
echo USERNAME $linename
echo USER "$linename's" DISK USAGE
sqlplus -s $linename/cmms << EOF
Select sum(bytes)/1024/1024 Mb, tablespace_name from user_segments group by tablespace_name;
exit;
EOF
done
)
}
function removeTempFile {
rm $filename
}
init
getUserName
readUserNamesandCheckDiskUsage
removeTempFile
exit ${SUCCESS}
No comments:
Post a Comment