tablespace_report_databases

00 01,06,18,23 * * * /home/oracle/DBAENGG/scripts/ofr_tbs.ksh >/tmp/tbs_space_check.log

pbpmccdbaora2:/opt/oracle/DBATOOLS/sql/general>>cat /home/oracle/DBAENGG/scripts/ofr_tbs.ksh
#!/bin/ksh
# set -vx
# Placed this script to collect critical TBS SPACE USAGE information
. $HOME/.setup_PMCGRD20.ksh
# export TNS_ADMIN=/opt/oracle/PWHGRD20/product/dbms/10.2.0/network/admin
# MAILLIST="nitesh.x.sharma@jpmchase.com"
# MAILLIST="sathish.panjala@jpmchase.com"
MAILLIST1="ib.core.svcs.oracle.mbox@jpmorgan.com"
MAILLIST="ib_global_dba@jpmchase.com"

DIR=/opt/oracle/DBAENGG/scripts
OUTTBS=$DIR/tbs_space.dat
log1=$DIR/tbs_spacemon_run_all.log
>$OUTTBS
echo "=== Oracle Tablespace space usage information  ====" > $log1
echo '**  Run Date: ' `date` >> $log1
echo " -----------------------------------------------------------------------------------------------------------------">> $log1
echo " Database         Tablespace                      Size (MB)    Free (MB)   Free %" >> $log1
echo " -----------------------------------------------------------------------------------------------------------------">> $log1
v_gdb=0
v_bdb=0
for ORATABLN in `cat /opt/oracle/DBAENGG/dbmap/oratab.database.tbs`
do
dbname=`echo $ORATABLN | awk -F: '{print $1}' -`
abc=$(sqlplus -s -L dbsnmp/umagellan1#@$dbname <<EOF
spool $DIR/tbs_spacemon.out
@$DIR/tbs_mon.sql
spool off
exit;
EOF)
if [ $? -ne 0 ] ; then
 NOW=`date '+%H:%M:%S'`
 echo 'Check At ' $NOW ':' 'Error Connect to ' $dbname' Database ..' >> $log1
 echo '** Error info : '$abc '..' >> $log1
 v_bdb=`expr $v_bdb + 1`
 else
# cat $DIR/tbs_spacemon.out >> $OUTTBS
 cat $DIR/tbs_spacemon.out >> $log1
 v_gdb=`expr $v_gdb + 1`
fi
done
echo " -------------------------------------------------------------------------">> $log1
echo "| Total Database Scanned:" $v_gdb '                                             |' >>  $log1
echo "| Total Database Not Scanned:" $v_bdb '                                                  |' >>  $log1
echo " -------------------------------------------------------------------------">> $log1
# send MAIL
mail -s "ORA Exception REPORT: Tablespace Space Check" $MAILLIST1 -c $MAILLIST < $log1


pbpmccdbaora2:/opt/oracle/DBATOOLS/sql/general>>cat /opt/oracle/DBAENGG/dbmap/oratab.database.tbs
CVPROD
NEOTKP
NEOKRP
NEOKRP-krlneop2
krneop
HKNEO
CERDCORP
NEOHKP
SGNEOP
INNEOP
NEOTWP
TWNEOU

pbpmccdbaora2:/opt/oracle/DBATOOLS/sql/general>>cat /opt/oracle/DBAENGG/scripts/tbs_mon.sql
set feedback off
set heading off
set verify off
set trimspool on
set linesize 150
set pagesize 500
col tablespace_name FORMAT A25
col "Size (MB)" for a12
col "Max Size(MB)" for a12
column LOB format a8 head 'LOB#'
column APP format a35 head 'APP#'
column pthread format a10 head 'Thread#'
column dname format a20 head 'Primary'
column sname format a20 head 'Standby'
break on name
SELECT d.name, F.tablespace_name "Tablespace", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990') "Size (MB)", TO_CHAR(NVL(NVL(f.bytes, 0),0)/1024/1024, '99G999G990') "Free (MB)", TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %" FROM (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f, v$database d WHERE f.tablespace_name=a.tablespace_name and F.tablespace_name  in (select b.tablespace_name
 from (select tablespace_name, sum(bytes)/1024/1024 free
        from dba_free_space
       group by tablespace_name) a,
     (select tablespace_name, sum(bytes)/1024/1024 total,
             sum(decode(autoextensible,'YES',maxbytes,bytes))/1024/1024 maxgrowth
        from dba_data_files
       group by tablespace_name) b
 where a.tablespace_name(+) = b.tablespace_name and (((total-nvl(free,0))/maxgrowth)*100) >85
and a.tablespace_name not like 'UNDO%' and a.tablespace_name not like 'TEMP%'
and (((total-nvl(free,0))/total)*100) >85);