#!/bin/sh
#
# $TOOLS/dbo_sql
#
# Displays a report of the sql statement currently being executed by all
# current users for the specified database ($1)
# (Default of all databases)
#
#	Last Change 02/20/97 by Brian Lomasky
#
# To allow a non-privileged user access to this script, as oracle, type:
#
#     makeops <SID> <USERNAME>
#     grantool
#          <SID>
#          OPS$<USERNAME>
#
#        where:  <SID> is the Oracle SID of the database to access
#                <USERNAME> is the username (without any OPS$)
#     - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
#     Note that grantool simply executes the following commands:
#		grant select on v$lock to <ops$username>;
#		grant select on v$session to <ops$username>;
#		grant select on v$sqlarea to <ops$username>;
#		grant select on dba_objects to <ops$username>;
#	(This presumes that your current username has the WITH GRANT OPTION on
#	 these privileges so that you can grant these privileges to someone
#	 else).
#
echo ""
echo "Displays a report of the sql statements for all current users of a" \
	"database"
if [ "$1" != "" ]
then
	mysid=`fixcase $1`
	outfil="dbo_sql.lis"
else
	mysid=""
	outfil="dbo_sql.lis"
fi
ORIIFS="$IFS"
IFS="~"
for LINE in `cat $TOOLS/database`
do
	#
	# Extract the first parameter as the database SID
	#
	ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
	#
	# Process only if first field matches specified SID or no
	# parameter was specified
	#
	if [ "$ORACLE_SID" = "$mysid" -o "$mysid" = "" ]
	then
		SID=$ORACLE_SID
		. define
		is_dbo $ORACLE_SID
		if [ $? -eq 1 ]
		then
			export ORACLE_SID
			sqlplus -s / @$TOOLS/dbo_sql $ORACLE_SID
			if [ -r $outfil ]
			then
				cat dbo_sql.lst >> $outfil
				rm dbo_sql.lst
			else
				mv dbo_sql.lst $outfil
			fi
		fi
	fi
done
IFS="$ORIIFS"
#
#     Display the report file
#
cat $outfil
rm $outfil