How to drop and create tablespace and users in Oracle

Jephe Wu - http://linuxtechres.blogspot.com


Objective: fully drop the existing tablespace and users in Oracle 11g and create a new ones
Environment: Oracle 11g 64bit, RHEL 5



Scripts:
 #!/bin/sh

if [ $# -ne 1 ];then echo "usage: $0  schema_name";exit;fi

SCHEMA=`echo $1 | tr [a-z] [A-Z]`



# create drop tablespace sql script
cat > /tmp/droptablespace.sql << END
drop tablespace $1 including contents and datafiles;
exit;
END

# create script to kill all existing connections
cat > /tmp/killusersession.sql <<END
set head off feedback off pagesize 0 echo off term off linesize 32767 trimsp on tab off define off;
spool /tmp/killusersessionfinal.sql;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where username='$SCHEMA';
select 'exit;' from dual;
spool off;
exit;
END

# create user creation sql script
cat > /tmp/dropuser.sql << END
drop user $1 cascade;
exit;
END

sqlplus / as sysdba @/tmp/droptablespace.sql
sqlplus / as sysdba @/tmp/killusersession.sql
sqlplus / as sysdba @/tmp/killusersessionfinal.sql
sqlplus / as sysba @/tmp/dropuser.sql

cat > /tmp/createtablespaceanduser.sql <<END
create tablespace  datafile '/u01/app/oracle/oradata/orcl/${1}.dbf' size 100m autoextend on next 10m;
create user $1 identified by password default tablespace $1 temporary tablespace temp;
exit;
END

sqlplus / as sysdba @/tmp/createtablespaceanduser.sql