#!/bin/sh
#
# location
#
# Creates a report containing the location of all database files, grouped by
# every directory, for every ORACLE SID on this computer.
#
# Reads the oratab file to locate all of the ORACLE SIDs on this computer.
#
# Restrictions:
#	1) An OPS$username must exist in each database.
#	2) This script creates various temporary files:
#	   location.lis, location.sh, location.tmp, location.tm2
#	3) This script creates temporary tables in the first available
#	   database that is found in the oratab file:
#		location_all_db_temp, location_locate_temp,
#		location_locate_temp2
#	4) Executes the following files:
#		locate1.sql, locate2.sql
#
# Last Change 06/26/97 by Brian Lomasky
#
if [ -r /etc/oratab ]
then
	ORATAB=/etc/oratab
else
	if [ -r /var/opt/oracle/oratab ]
	then
		ORATAB=/var/opt/oracle/oratab
	else
		echo "Can't find any oratab file to read - Aborting...\007"
		exit 1
	fi
fi
echo "set feedback off" > location.tm2
echo "set termout off" >> location.tm2
echo "drop table location_all_db_temp;" >> location.tm2
echo "set termout on" >> location.tm2
echo "create table location_all_db_temp (" >> location.tm2
echo "filespec varchar2(80)," >> location.tm2
echo "sid varchar2(8)," >> location.tm2
echo "type varchar2(4)," >> location.tm2
echo "tablespace varchar2(30)," >> location.tm2
echo "mbytes number," >> location.tm2
echo "status varchar2(5));" >> location.tm2
echo "" >> location.tm2
if [ -r location.tmp ]
then
	rm location.tmp
fi
if [ -r location.lis ]
then
	rm location.lis
fi
if [ -r location.sh ]
then
	rm location.sh
fi
echo "Reading $ORATAB..."
#
# Process each entry in the oratab file
#
cat $ORATAB | while read LINE
do
	case $LINE in
	\#*)	;;			#comment-line in oratab
	*)
		ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
		if [ "$ORACLE_SID" != '*' ]
		then
			export ORACLE_SID
			ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
			export ORACLE_HOME
			# Put $ORACLE_HOME/bin into PATH and export
			PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc; export PATH
			# Skip database if it is not started
			if test -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf -o \
				-f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.ora
			then
				echo "Examining database ${ORACLE_SID}..."
				sqlplus -s / @locate1 $ORACLE_HOME \
					$ORACLE_SID
				if [ -r location.tmp ]
				then
					cat location.tmp >> location.tm2
					rm location.tmp
					if [ ! -r location.sh ]
					then
						echo "ORACLE_SID=$ORACLE_SID" \
							> location.sh
						echo "export ORACLE_SID" >> \
							location.sh
						echo "ORACLE_HOME=$ORACLE_HOME"\
							>> location.sh
						echo "export ORACLE_HOME" >> \
							location.sh
						echo "PATH=$ORACLE_HOME/bin:"\
"/bin:/usr/bin:/etc; export PATH" >> location.sh
						echo "sqlplus -s /" \
							" @location.tm2" >> \
							location.sh
						echo "sqlplus -s /" \
							" @locate2" >> \
							location.sh
						chmod 700 location.sh
					fi
				fi
			else
				echo "Skipping database ${ORACLE_SID}," \
					"since it is not running..."
			fi
		fi
	esac
done
echo "exit" >> location.tm2
if [ -r location.sh ]
then
	echo "Creating report..."
	./location.sh
	rm location.sh location.tm2
	if [ -r location.lis ]
	then
		echo ""
		echo "Created location.lis report file..."
	fi
else
	echo "No report created, since no databases were found!\007"
	rm location.tm2
fi