#!/bin/sh
#
# Copyright (c) 1998 by Sun Microsystems, Inc.
#
# $Id: schema_C.sh,v 1.6 2001/04/28 00:21:44 ramesh Exp $
# 
#
# Script to create corp tables
#
# Tom Daly Jan 2000 
# Upated by Tai-Wei Lin July 2001 for eBenchmark
#     modify script to build corp specific 
#     tables and tablespaces against an existing database
#     This is to allow all of ECperf to run on one database
#     which will allow for easier testing and installation.
#     schema_C.sh now takes database name and database directory 
#     parameters.
#     Note: The database must now exist, refer to the createdb.sh script
#     to build the database prior to running this script.
#                     

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

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

DB=$1
DB_DIR=$2
ORACLE_SID=$DB
export ORACLE_SID

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

STATSPACK=${DB_DIR}/statspack
STATSPACKTEMP=${DB_DIR}/statspack_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 statspack_space INCLUDING CONTENTS;
DROP TABLESPACE statspack_temp  INCLUDING CONTENTS;
EOT

echo "Removing Datafiles"
rm -f $STATSPACK $STATSPACKTEMP


##############################################
# Create the required tablespaces
##############################################
sqlplus /nolog <<EOT
CONNECT system/manager 
CREATE TABLESPACE statspack_space
	DATAFILE '${STATSPACK}' SIZE 100m 
	DEFAULT STORAGE ( INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0);
EXIT
EOT

sqlplus /nolog <<EOT
CONNECT system/manager 
CREATE TABLESPACE statspack_temp
	DATAFILE '${STATSPACKTEMP}' SIZE 100m 
	DEFAULT STORAGE ( INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;
EXIT
EOT


##############################################
# install statspack
##############################################
sqlplus /nolog <<EOT
connect / as sysdba
@?/rdbms/admin/spcreate ;
perfstat
statspack_space
statspack_temp
EXIT
EOT


wait


