. $ENV_FILE
#sqlplus -silent / as sysdba << EOF
sqlplus -silent $ADMIN_USER/$ADMIN_PASSWD << EOF
set echo off
set numwidth 9
Set head off
set feedback off
set verify off
set time off
set timing off
set termout off
set pages 5000
set lines 150
column file_name format a50
column file_id format 9999 heading ‘FLID’
column tablespace_name format a20
column free_mb format a8
column maxsize_mb format 9999999
column free_pct format 99 heading ‘FREE|%’
column EXTMB format 999
column AE format a3 heading Auto|XTND|ON?
spool $LOG_FILE_DIR/chk_space_$ORACLE_SID
select /*+ RULE */
a.tablespace_name, a.file_id ,a.file_name,
nvl( to_char(trunc(decode(a.autoextensible,’YES’,a.maxsize-a.bytes+b.free,’NO’,b.free)/1024/1024)),’NA’) free_mb,
trunc(a.bytes/1024/1024) size_mb ,trunc(a.maxsize/1024/1024) maxsize_mb ,a.autoextensible ae ,
decode (c.allocation_type, ‘SYSTEM’, 0, round(c.next_extent/(1022 * 1024), 0)) EXTMB, c.allocation_type
from
(select file_id , file_name, tablespace_name ,autoextensible ,bytes ,decode(autoextensible,’YES’,maxbytes,bytes) maxsize
from dba_data_files ) a,
(select file_id, tablespace_name, sum(bytes) free
from dba_free_space
group by file_id, tablespace_name) b,
dba_tablespaces c,
(select /*+ ALL_ROWS */ file_id, count(1) EXT# from dba_extents group by file_id) d
where a.file_id=b.file_id(+)
and a.tablespace_name=b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
and a.file_id = d.file_id(+)
order by a.tablespace_name asc;
spool off
exit;
EOF
IFS=’
‘
line_counter=1;
TS_OK=`echo “N”`;
SEND_NOTIFICATION=`echo “N”`;
tot_lines=`cat $LOG_FILE_DIR/chk_space_$ORACLE_SID.lst |wc -l`;
for line in `cat $LOG_FILE_DIR/chk_space_$ORACLE_SID.lst`
do
v_tsname=`echo $line | awk ‘{print $1}’`;
v_fileid=`echo $line | awk ‘{print $2}’`;
v_filenm=`echo $line | awk ‘{print $3}’`;
v_freemb=`echo $line | awk ‘{print $4}’`;
v_sizemb=`echo $line | awk ‘{print $5}’`;
v_mxsize=`echo $line | awk ‘{print $6}’`;
v_autxtn=`echo $line | awk ‘{print $7}’`;
v_extsz=`echo $line | awk ‘{print $8}’`;
v_allotype=`echo $line | awk ‘{print $9}’`;
v_mountsz=`df -k $v_filenm | awk ‘{print $4}’ | sed ‘1d’`;
let v_mountsz=$v_mountsz/1024;
# print “ts-name : $v_tsname”;
# print “fileid : $v_fileid”;
# print “filenm : $v_filenm”;
# print “freemb : $v_freemb”;
# print “sizemb : $v_sizemb”;
# print “mxsize : $v_mxsize”;
# print “autxtn : $v_autxtn”;
# print “extsz : $v_extsz”;
# print “allotype : $v_allotype”;
# print “mountsz : $v_mountsz”;
# print “Line is as below”;
# print $line;
# print “Line Ends”;
if [ $line_counter -eq 1 ]
then
prv_tsname=$v_tsname;
fi
if [ $prv_tsname = $v_tsname -a $TS_OK = “Y” ]
then
# /* continue with next line from file as this tablespace has free space available, no need to check other datafiles; */
let line_counter=$line_counter+1;
if [ $line_counter -eq $tot_lines -a $TS_OK = “N” ]
then
echo “Tablespace $v_tsname WILL BE UNABLE TO EXTEND”;
SEND_NOTIFICATION=`echo “Y”`;
elif [ $line_counter -eq $tot_lines -a $TS_OK = “Y” ]
then
echo “Tablespace $v_tsname is OKAY”;
fi
continue;
elif [ $prv_tsname != $v_tsname -a $TS_OK = “N” ]
then
# /* Raise Error for previous TS and write that error in a log. */
print “Tablespace $prv_tsname WILL BE UNABLE TO EXTEND”;
SEND_NOTIFICATION=`echo “Y”`;
elif [ $prv_tsname != $v_tsname -a $TS_OK = “Y” ]
then
print “Tablespace $prv_tsname is OKAY”;
fi
#/*
# — prv_ts != v_tsname and TSOK =N then error message like prv TS has problem
# — prv_tsname = vtsname and TSOK =N then following code needs to execute…. for curren TS
# — prv ts != v_ts name and TSOK=Y then follwoign code need to execute…for current TS.
#*/
#/* Following code will set TSOK and prv_tsname values */
if [ $v_freemb = “NA” ]
then
let free_space=$v_mxsize-$v_sizemb;
else
let free_space=$v_freemb;
fi
# print “ts: $v_tsname and fname=$v_filenm and free_space=$free_space and v_extsz=$v_extsz”;
# following condition is for SYSTEM allocated extents..Where Extent size is unavailable. So checking only enough free space on mount point.
if [ $v_extsz = “NA” ]
then
if [ $v_mountsz -lt $SYSTEM_ALLOCATED_THRESHOLD ]
then
TS_OK=`echo “N”`;
SEND_NOTIFICATION=`echo “Y”`;
else
TS_OK=`echo “Y”`;
fi
else
# following condition is for UNIFORM extents…
if [ $v_extsz -le $free_space ]
then
if [ $v_extsz -le $v_mountsz ]
then
TS_OK=`echo “Y”`;
else
TS_OK=`echo “N”`;
fi
else
TS_OK=`echo “N”`;
fi
fi
prv_tsname=$v_tsname;
if [ $line_counter -eq $tot_lines -a $TS_OK = “N” ]
then
echo “Tablespace $v_tsname WILL BE UNABLE TO EXTEND”;
SEND_NOTIFICATION=`echo “Y”`;
elif [ $line_counter -eq $tot_lines -a $TS_OK = “Y” ]
then
echo “Tablespace $v_tsname is OKAY”;
fi
let line_counter=$line_counter+1;
# print “line_counter=$line_counter”;
done
print “SEND_NOTIFICATION=$SEND_NOTIFICATION”;
if [ $SEND_NOTIFICATION = “Y” ]
then
mailx -s “$ORACLE_SID at $NODENAME1 : Impending Next Extent Failure” $NOTIFY_LIST<
Also execute ts2.sql to identify exact tablespace that will be unable to extend.
Next Extent Allocation will fail for indicated tablespaces.
EOF
fi