Thursday 13 January 2011

Schema or User Disk Usage in Oracle

#!/bin/bash

########### USER INPUTS ###########
sys_username="system"
sys_password=""
schema_name_string="" #Input standard name used in all tables.
########### 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

Azure OpenAI Architecture Patterns & Deployment Patterns

Sharing some useful links that will help customers architect Azure OpenAI solution using the best practices: (1) Azure OpenAI Landing Zone r...