#!/bin/ksh #******************************************************************************* # KSH PROGRAM #******************************************************************************* # # PROGRAM ID : db_space_report.ksh # #D DESCRIPTION : Provide an overview of the space situation of a #D database #D # # INPUT PARAMETERS : $1 - ORACLE_SID # $2 - User ID # $3 - Password # # OUTPUT PARAMETERS : none # # # INPUT FILES : none # # OUTPUT FILES : /app/orcl/oradata/ORCL/dump/DBA/reports/db_space_report___.out # : /tmp/db_space_report___.err # # # SPECIAL LOGIC NOTES : none # #******************************************************************************* # MODIFICATION LOG # # # DATE SE # DESCRIPTION # --------------------------------------------------------------------------- # Thu Jan 29 14:48:23 CET 2015 (Hajo) # initial version # --------------------------------------------------------------------------- # #******************************************************************************* #******************************************************************************* # INITIALIZATION #******************************************************************************* #------------------------------ # program aliasses #------------------------------ alias awk='/usr/bin/awk' alias sed='/usr/bin/sed' #------------------------------ # init variables #------------------------------ export s_myName=`basename $0` # remember my name export s_myNameBase=$(echo ${s_myName} | sed -e's/\.ksh//') export s_parmcount=$# export s_sek=`date +'%S'` export s_min=`date +'%M'` export s_std=`date +'%H'` export s_tag=`date +'%d'` export s_mon=`date +'%m'` export s_jhr=`date +'%Y'` export s_now="$s_jhr$s_mon$s_tag$s_std$s_min$s_sek" export s_parmcountExpected=3 # initiate expected number of input parameters export s_rc=0 # initiate return code if [[ $s_parmcount -ne $s_parmcountExpected ]] then print -u2 "`basename $0`: USAGE: `basename $0` !" exit 1 else export s_instance=$(echo $1 | tr '[:lower:]' '[:upper:]') export s_user=$2 export s_pw=$3 fi export s_host=$(hostname) export s_oratab=/etc/oratab export s_report_dir=/app/orcl/oradata/ORCL/dump/DBA/reports export s_report_file=${s_report_dir}/${s_myNameBase}_${s_host}_${s_instance}_${s_now}.out export s_error_file=/tmp/${s_myNameBase}_${s_host}_${s_instance}_${s_now}.err export ORACLE_SID=${s_instance} grep ${ORACLE_SID} ${s_oratab} >/dev/null 2>&1 ;s_rc=$? if [[ $s_rc -ne 0 ]] then print "ERROR: <${ORACLE_SID}> not found in ORATAB <${s_oratab}>!" >${s_error_file} exit $s_rc fi export ORACLE_HOME=$(grep $ORACLE_SID /etc/oratab | sed -e's/\:/ /g' | awk '{print $2}') export PATH=${PATH}:${ORACLE_HOME} #******************************************************************************* # MAIN #******************************************************************************* sqlplus -s $s_user/$s_pw <<-EOSQL > ${s_error_file} 2>&1 set pages 1000 set lines 150 set heading off set feedback off spool ${s_report_file} select 'Report Date: ' || to_char(sysdate,'YYYY-MM-DD_HH24:MI:SS') "Report Date" from dual; set heading on col "Tablespace" for a22 col "Used MB" for 99,999,999 col "Free MB" for 99,999,999 col "Total MB" for 99,999,999 select df.tablespace_name "Tablespace", totalusedspace "Used_MB", (df.totalspace - tu.totalusedspace) "Free_MB", df.totalspace "Total_MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct_Free" from (select tablespace_name, round(sum(bytes) / 1048576) totalspace from dba_data_files group by tablespace_name) df, (select round(sum(BYTES)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name order by 1; prompt column tablespace_name format a20 column file_name format a75 select tablespace_name "Tablespace", file_name "File_Name", round(bytes/1024/1024) "MB", autoextensible "AE" from dba_data_files order by 1; spool off quit EOSQL #******************************************************************************* # FINALIZATION #******************************************************************************* chmod 644 ${s_report_file} if [[ -s ${s_error_file} ]] then echo nop >/dev/null else rm ${s_error_file} >/dev/null 2>&1 fi #****************************************************************************** # FINE #******************************************************************************* exit