Matt Calvert's Blog

(Script) Oracle Tablespace Usage Report

#!/bin/bash
################################################################################
# Program:           ts_rpt.sh
#
# Version Number:    1.0
#
# Description:       Daily tablespace reporting.
# Created:           19-Sep-2012
#
# Amendments                                            Who     When
# --------------------------------------------------------------------
#
################################################################################

#### Set Oracle Environment Variables ############
#
if [ "$1" ]
then
        export ORACLE_SID=$1
else
        echo "Usage: $0 <ORACLE_SID>" >&2
        exit 1
fi

#
# Set database specific variables
#
. $HOME/local/${ORACLE_SID}.env

#### Set Variables ############
#
HOSTNAME=`hostname -s`

#
# Set Logfile
#
export LOGFILE=$DB_LOG_DIR/ts_rpt.${HOSTNAME}.${ORACLE_SID}-`date +%a`.log
cp /dev/null ${LOGFILE}
echo "Starting $0 at `date`" | tee -a ${LOGFILE}

# Connect to  databaseA and run SQL.

sqlplus -s '/ as sysdba' >>${LOGFILE} << EOF
column tablespace_name headin "Tablespace Name"
column total_mb format 9,999,999 heading "Total (MB)"
column free_mb format 9,999,999 heading "Free (MB)"
column pct_used format 999.9 heading "Used|(%)"

select
  d.tablespace_name, d.bytes/1024/1024 as total_mb,
  f.bytes/1024/1024 as free_mb,
  100*(d.bytes-f.bytes)/d.bytes as pct_used
from
  (
    select tablespace_name, sum(bytes) as bytes
    from dba_data_files
    group by tablespace_name
  ) d
  left outer join (
    select tablespace_name, sum(bytes) as bytes
    from dba_free_space
    group by tablespace_name
  ) f on d.tablespace_name = f.tablespace_name
order by
  d.tablespace_name
/

column name form a20 heading "ASM Disk Group"
column disks for 99999 heading "Disk|count"
column min_mb format 9,999,999 heading "Free (MB)"

select
  g.name, count(*) as disks,
  sum(d.total_mb) as total_mb,
  min(d.free_mb) * count(*) as min_mb,
  100*(sum(d.total_mb)-min(d.free_mb)*count(*))/sum(d.total_mb) as pct_used
from
  v\$asm_disk d inner join v\$asm_diskgroup g on d.group_number = g.group_number
group by
  g.name
order by
  g.name
/
EXIT
EOF

echo "sending report..." | tee -a ${LOGFILE}
EVENT=TS_RPT
SUBJECT="Application DB space report"

###Transmit MAIL ${EVENT} ${SUBJECT} ${ORACLE_SID}
( \
        echo "Application DB space report"; \
        echo "======================================"; \
        echo ""; \
        cat ${LOGFILE}
) | mailx -s "${SUBJECT}" admin@domain.co.uk sysadmin@domain.co.uk