Create user with its own tablespaces

set    echo off

column datum new_value datum;
select to_char(sysdate,'YYYYMMDD') datum from dual;

column oracleid new_value oracleid;
select instance_name oracleid from v$instance;

-- -----------------------------------------------------------------------------
-- Check to what filesystem the TBS goes *_DT=oradata04 *_IX=oradata05
--
-- To run, type sqlplus "/ as sysdba" @create_user <schema_name> <schema_password>
-- -----------------------------------------------------------------------------

def oracleschema=&1
def oraclepasswd=&2

prompt "Are these details correct?"
prompt "=========================="
prompt "Oracle SID   => " &&oracleid
prompt "oracleschema => " &&oracleschema
prompt "oraclepasswd => " &&oraclepasswd.
pause   Press cntrl-d if not correct

set   echo on

spool create_user_&&oracleschema._&&datum..log


-- ------------------------------------------------------------------
-- create permanent and temp tablespaces (if they dont exist already)
-- ------------------------------------------------------------------
create tablespace &oracleschema._DT datafile '/oracle/&oracleid./oradata4/&oracleschema._DT01.dbf' size 100M
       autoextend on next 100M maxsize 20000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

create tablespace &oracleschema._IX datafile '/oracle/&oracleid./oradata5/&oracleschema._IX01.dbf' size 100M
       autoextend on next 100M maxsize 20000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--create BIGFILE tablespace WMDATA datafile '/oracle/&oracleid./oradata4/wmdata.dbf' size 100M autoextend on next 100M;
--create BIGFILE tablespace WMINDX datafile '/oracle/&oracleid./oradata5/wmindx.dbf' size 100M autoextend on next 100M;


-- ---------------
-- create the user
-- ---------------
create user &oracleschema identified by rubbish password expire profile oracle_schema_user default tablespace &oracleschema._DT temporary tablespace TEMP;
alter  user &oracleschema identified by &oraclepasswd;
alter  user &oracleschema. quota unlimited on &oracleschema._DT;
alter  user &oracleschema. quota unlimited on &oracleschema._IX;
alter user  &oracleschema. default role all;


-- ------------------------------------------
-- grants to create objects in its own schema
-- ------------------------------------------
grant connect to &oracleschema
/
@/home/ibmtools/scripts/oracle/create_role_ibm_create_objects.sql

spool create_user_&&oracleschema._&&datum..log append

grant ibm_create_objects to &oracleschema.
/


-- ----------
-- validation
-- ----------
connect &oracleschema./&oraclepasswd.
create table dummy_test (name varchar2(10));
insert into dummy_test values ('wim');
insert into dummy_test select * from dummy_test;
/
/
/
drop table dummy_test;


spool off
exit;


-- drop tablespace &&oracleschema._DT  including contents and datafiles;
-- drop tablespace &&oracleschema._IX  including contents and datafiles;
-- drop user  &&oracleschema. cascade;