#!/bin/ksh

cd /home/orapfpr

. .profile

export ts=$1

echo $ts | cut -c8-11 | read tshr

#export ts=`date +%y%m%d_%H%M%S`

cd /home/orapfpr/scripts/space_mgmt/scripts

export writes_threshold=15

export SPACE_MGMT_HOME=/home/orapfpr/scripts/space_mgmt

export DIFF_HOME=$SPACE_MGMT_HOME/config/diff_files

export FREE_XTN_HOME=$SPACE_MGMT_HOME/config/free_extent_data

export ALLOC_XTN_HOME=$SPACE_MGMT_HOME/config/alloc_extent_data

export NEXT_EXTENT_HOME=$SPACE_MGMT_HOME/config/next_extent_data

export LOGFILE_HOME=$SPACE_MGMT_HOME/log_files

export CONFIG_HOME=$SPACE_MGMT_HOME/table_files

export HISTORY_HOME=$SPACE_MGMT_HOME/table_history_files

export FXF=$FREE_XTN_HOME/sorted_free_extents.$ts

export NXF=$NEXT_EXTENT_HOME/next_extents.$ts

export PHYWRT_HOME=$SPACE_MGMT_HOME/config/physical_writes

export PHYWRT_DIFF_HOME=$SPACE_MGMT_HOME/config/physical_write_diffs

export NNE_HOME=$SPACE_MGMT_HOME/config/no_next_extent

export DBA3=gs1114@stl.rural.usda.gov

export DBA2=lm1076@stl.rural.usda.gov

export DBA1=lb1088@stl.rural.usda.gov

export LOC1=Mitchell.Clem@Unisys.com

export PAGER1="3142450893@archwireless.net"

#touch /home/orapfpr/scripts/space_mgmt/config/next_extent_data/*

#rm /home/orapfpr/scripts/space_mgmt/config/next_extent_data/*

touch $SPACE_MGMT_HOME/config/no_next_extent/untables.$ts

rm $SPACE_MGMT_HOME/config/no_next_extent/untables.$ts

touch $SPACE_MGMT_HOME/config/no_next_extent/all_untables

rm $SPACE_MGMT_HOME/config/no_next_extent/all_untables

touch $SPACE_MGMT_HOME/config/no_next_extent/largest_free_extents_file

rm $SPACE_MGMT_HOME/config/no_next_extent/largest_free_extents_file

touch $SPACE_MGMT_HOME/config/no_next_extent/largest_next_from_os_file

rm $SPACE_MGMT_HOME/config/no_next_extent/largest_next_from_os_file

touch $NNE_HOME/tablespace_list/active_tablespace_list.$ts

#rm $NNE_HOME/tablespace_list/active_tablespace_list.$ts

ls "$PHYWRT_HOME"/physical_write_data.* | tail -2 | head -1 | read previous_writes_scan

ls "$PHYWRT_HOME"/physical_write_data.$ts | tail -1 | read current_writes_scan

cat "$previous_writes_scan" "$current_writes_scan" | sort -n > $PHYWRT_DIFF_HOME/catfile.$ts

more $PHYWRT_DIFF_HOME/catfile.$ts | wc -l | read m

let n=2

while [ "$n" -le "$m" ]

do

more $PHYWRT_DIFF_HOME/catfile.$ts | head -"$n" | tail -2 | head -1 | cut -f4 -d':' | read writes_1

more $PHYWRT_DIFF_HOME/catfile.$ts | head -"$n" | tail -2 | head -1 | cut -f2 -d':' | read tspc_1

more $PHYWRT_DIFF_HOME/catfile.$ts | head -"$n" | tail -2 | tail -1 | cut -f4 -d':' | read writes_2

more $PHYWRT_DIFF_HOME/catfile.$ts | head -"$n" | tail -2 | tail -1 | cut -f2 -d':' | read tspc_2

if [ "$tspc_1" = "$tspc_2" ]

then

let delta=$writes_2-$writes_1

fi

if [ "$delta" -ge "$writes_threshold" ]

then

echo $tspc_1 >> $NNE_HOME/tablespace_list/active_tablespace_list.$ts

fi

let n=$n+2

done

more $NNE_HOME/tablespace_list/active_tablespace_list.$ts | sort -u > $NNE_HOME/tablespace_list/active_tablespace_list_2

mv $NNE_HOME/tablespace_list/active_tablespace_list_2 $NNE_HOME/tablespace_list/active_tablespace_list.$ts

let i=1

let j=1

let k=2

more $NNE_HOME/tablespace_list/active_tablespace_list.$ts | wc -l | read n

while [ "$j" -le "$n" ]

do

more $NNE_HOME/tablespace_list/active_tablespace_list.$ts | head -"$j" | tail -1 | read tablespace_name

echo $tablespace_name':' | read tspc_name

more $FXF | grep "$tspc_name" | cut -f3 -d':' | sort -u | sort -n | tail -1 | read lgst_free

more $NXF | grep "$tspc_name" | cut -f3 -d':' | sort -u | sort -n | tail -"$i" | head -1 | read lgst_nxt

while [ "$lgst_nxt" -gt "lgst_free" ]

do

#more $NXF | grep "$lgst_nxt" | grep "$tspc_name" | cut -f1,2 -d':' | sort -u

more $NXF | grep "$lgst_nxt" | grep "$tspc_name" | cut -f1,2 -d':' | sort -u >> $NNE_HOME/untables.$ts

more $NXF | grep "$tspc_name" | cut -f3 -d':' | sort -u | sort -n | tail -"$k" | head -1 | read second_lgst_nxt

if [ "$second_lgst_nxt" -eq "$lgst_nxt" ]

then

let lgst_nxt=0

else

let lgst_nxt=$second_lgst_nxt

fi

let k=$k+1

let j=$j+1

done

let j=$j+1

let i=1

let k=2

done

touch $NNE_HOME/untables.$ts

more $NNE_HOME/untables.$ts | wc -l | read tables_attend

if [ "$tables_attend" -gt 0 ]

then

#mailx -m -s "ORA-1653 No-Next-Extent Error Possible" $DBA3 < $NNE_HOME/untables.$ts

#mailx -m -s "ORA-1653 No-Next-Extent Error Possible" $DBA3 $DBA2 $DBA1 $LOC1 $PAGER1 < $NNE_HOME/untables.$ts

mailx -m -s "ORA-1653 No-Next-Extent Error Possible" $DBA3 $DBA2 $DBA1 $PAGER1 < $NNE_HOME/untables.$ts

else

rm $NNE_HOME/untables.$ts

fi