#####################################################################################
MONITOR SCRIPT – it will monitor objects close to max extents, tablespace free space
#####################################################################################
#! /bin/sh
TS=`date “+%y%m%d”`
ORACLE_HOME=/d001/oracle/9.2.0.8-64
LD_LIBRARY_PATH=$ORACLE_HOME/lib
ORACLE_TERM=vt100
ORACLE_SID=ORVIT8QA
LOG=/d002/oracle/$ORACLE_SID/utilitys
PATH=/$ORACLE_HOME/bin:$PATH
export ORACLE_HOME ORACLE_SID ORACLE_TERM PATH LOG LD_LIBRARY_PATH
for n in 1
do
if [ $n -eq 1 ]; then
TWO_TASK=brcvita8_ORVIT8QA
DB_NAME=ORVIT8QA
fi
export TWO_TASK
monitor_file=$LOG/$DB_NAME.log
#n=`expr $n + 1`
rm $monitor_file
echo “Objects close to max extents:” >> $monitor_file
sqlplus -s system/systemorvit8QA << eof >> $monitor_file
set pagesize 100;
set linesize 80;
column segment_name format a35;
column type format a10;
column owner format a10;
column extents format 9999;
column max_extents format 9999;
select owner, segment_name, segment_type “TYPE”, extents, max_extents
from sys.dba_segments
where extents/greatest(max_extents,1) >= 0.90
and segment_type not in(‘CACHE’,’ROLLBACK’);
exit;
eof
echo “Objects that cannot get next extent:” >> $monitor_file
sqlplus -s system/systemorvit8QA << eof >> $monitor_file
set pagesize 100;
set linesize 80;
column segment_name format a33;
column type format a8;
column owner format a10;
column tablespace format a13;
column next_extent format 999999999;
select owner, segment_name, segment_type “TYPE”,
tablespace_name “TABLESPACE”, next_extent
from sys.dba_segments b
where segment_type !=’ROLLBACK’ and
next_extent > (select max(bytes) from sys.dba_free_space a
where a.tablespace_name = b.tablespace_name);
exit;
eof
echo “Tablespaces with low freespace (< 20% & < 250 MB):” >> $monitor_file
sqlplus -s system/systemorvit8QA << eof >> $monitor_file
set echo off
set pagesize 30
set feedback off;
drop table system.mon_smt_ts;
create table system.mon_smt_ts as
(select x.tablespace_name, round(sum(x.bytes)/1048576,1) freespace
from sys.dba_free_space x
group by x.tablespace_name);
drop table system.mon_smt_ts1;
create table system.mon_smt_ts1 as
(select tablespace_name, round(sum(bytes)/1048576,1) availspace
from sys.dba_data_files
group by tablespace_name);
set feedback on;
column “Table Space” format a20
column “Allocated” format a12
column “Free Space” format a12
column “Space Used” format a12
select x.tablespace_name “Table Space”,
to_char(availspace,’99999.9′)||’ Meg’ “Allocated”,
to_char(freespace,’99999.9′)||’ Meg’ “Free Space”,
round(freespace/availspace*100) “Pct Free”,
to_char((availspace-freespace),’99999.9′)||’ Meg’ “Space Used”
from system.mon_smt_ts x, system.mon_smt_ts1 y
where y.tablespace_name = x.tablespace_name
and round(freespace/availspace*100) < 20 and freespace<250
order by round(freespace/availspace*100);
exit;
eof
null_findings=`egrep “no rows selected” $monitor_file | wc -l`
null_findings=`echo $null_findings`
if [ “$null_findings” != 3 ]; then
/usr/bin/mailx -s “$DB_NAME Exception Report” dba_ora_offshore@bp.com < $monitor_file
fi
done
