#!/bin/sh
#

if [ "$ORACLE_HOME" = "" ]
then
	echo "$ORACLE_HOME"
	echo "You must set the ORACLE_HOME environment variable"
	exit 1
fi

if [ $# -ne 1 ]
then
	echo "Usage: schema_P.sh <database dir>"
	exit 1
fi

DB=specdb
DB_DIR=$1

######  datafiles #####

PERFSPACE=${DB_DIR}/perfspace
PERFTMPSPACE=${DB_DIR}/perfspace_temp

#sqlplus /nolog <<EOT  
#connect / as sysdba
#shutdown
#startup pfile=${ORACLE_HOME}/dbs/p_build_${DB}.ora
#EOT

##############################################
#  clean up tablespaces and 
#  datafiles from  earlier attempts
##############################################
echo "Cleaning up old tables spaces"
sqlplus /nolog <<EOT 
CONNECT system/manager 
DROP TABLESPACE perfspace INCLUDING CONTENTS;
DROP TABLESPACE perfspace_tmp INCLUDING CONTENTS;
EOT

echo "Removing Datafiles"
rm -f $PERFSPACE $PERFTMPSPACE


##############################################
# Create the required tablespaces
##############################################
sqlplus /nolog <<EOT
CONNECT system/manager 
CREATE TABLESPACE PERFSPACE
	DATAFILE '${PERFSPACE}' SIZE 1000M
	DEFAULT STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0);
EXIT
EOT
#	EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

sqlplus /nolog <<EOT
CONNECT system/manager 
CREATE TEMPORARY TABLESPACE PERFSPACE_TMP
	TEMPFILE '${PERFTMPSPACE}' SIZE 100M;
EXIT
EOT

##############################################
# Install STATSPACK
##############################################
sqlplus /nolog <<EOT
CONNECT / as sysdba
@/export/home/oracle/rdbms/admin/spdrop
@/export/home/oracle/rdbms/admin/spcreate
perfstat
PERFSPACE
PERFSPACE_TMP
EOT
