REM THIS SCRIPT IS CUSTOMIZED FROM crdb2.orc
REM 
REM *********************** I M P O R T A N T *******************************
REM WHEN YOU CREATE A DATABASE, COPY THIS SCRIPT TO crdb2TESTDB.sql and 
REM MODIFY ALL TESTDB OCCURENCES ON THIS FILE TO THE SID YOU WISH TO CREATE
REM MAKE SURE YOU HAVE ORACLE_HOME AND ORACLE_SID VALUES SET PROPERLY
REM 
REM TO CHANGE ALL TESTDB TO YOURDB, TYPE 
REM    1.  Esc
REM    2.  :
REM    3.  %s/TESTDB/YOURDB/g
REM **************************************************************************
REM
REM THIS SCRIPT CREATES THE MANDATORY TABLESPACES, ROLLBACK SEGMENTS
REM
REM THIS SCRIPT ALSO RUNS THE NECESSARY SCRIPTS FOR PROCEDURAL OPTION
REM
REM PLEASE CHANGE THE PASSWORD OF SYS AND SYSTEM ONCE THIS SCRIPT IS COMPLETE
REM

REM **************************************************************************
REM CREATE THE OFA COMPLIANT DATABASE
REM **************************************************************************
set termout on 
set echo on 
spool crdb2TESTDB.lst 

connect internal

REM **************************************************************************
REM INSTALL DATA DICTIONARY VIEWS
REM **************************************************************************
@${ORACLE_HOME}/rdbms/admin/catalog.sql

REM **************************************************************************
REM CREATE ADDITIONAL ROLLBACK SEGMENT IN SYSTEM BEFORE CREATING TABLESPACE
REM **************************************************************************
connect internal

create rollback segment rbs0 tablespace system
storage (initial 52k next 52k minextents 2 maxextents 40);

alter rollback segment rbs0 online;

REM **************************************************************************
REM CREATE A TABLESPACE FOR ROLLBACK SEGMENTS
REM **************************************************************************
create tablespace rbs datafile
       '/ora2/oradata/TESTDB/rbs01.dbf' size 100M
default storage (
       initial    512k next        512k    pctincrease   0
       minextents 2    maxextents  505     );

REM **************************************************************************
REM CREATE A TABLESPACE FOR TEMPORARY SEGMENTS
REM **************************************************************************
create tablespace temp datafile
       '/ora1/oradata/TESTDB/temp01.dbf' size 100m
default storage (
        initial      1M    next         1M
        pctincrease  0      maxextents   505);
alter tablespace temp temporary;

REM **************************************************************************
REM CREATE A TABLESPACE FOR DATABASE TOOLS
REM **************************************************************************
create tablespace tools datafile
       '/ora1/oradata/TESTDB/tools01.dbf'  size 50m
default storage (
        initial      1M     next         1M
        pctincrease  0      maxextents   500);

REM **************************************************************************
REM CREATE A TABLESPACE FOR MISCELLANEOUS DATABASE USER ACTIVITY
REM **************************************************************************
create tablespace users datafile
       '/ora2/oradata/TESTDB/user01.dbf' size 100m
default storage (
        initial      512k   next         512k
        pctincrease  0      maxextents   500);

REM **************************************************************************
REM CREATE ROLLBACK SEGMENTS
REM **************************************************************************
create rollback segment r01 tablespace rbs ;
create rollback segment r02 tablespace rbs ;
create rollback segment r03 tablespace rbs ;
create rollback segment r04 tablespace rbs ;

alter rollback segment r01 online;

REM **************************************************************************
REM SINCE WE'VE CREATED AND BROUGHT ONLINE 2 MORE ROLLBACK SEGMENTS,
REM WE NO LONGER NEED THE SECOND ROLLBACK SEGMENT IN THE SYSTEM TABLESPACE
REM **************************************************************************
alter rollback segment rbs0 offline;
drop rollback segment rbs0;

REM **************************************************************************
REM FOR EACH DBA USER, RUN DBA SYNONYMS SQL SCRIPT
REM **************************************************************************
set termout on
connect system/manager
set echo off 
@${ORACLE_HOME}/rdbms/admin/catdbsyn.sql
set echo on

spool off

REM **************************************************************************
REM RUN CATPROC.SQL FOR PROCEDURAL OPTION
REM **************************************************************************
spool crdb3TESTDB.lst

connect internal
set echo off
set termout on
@${ORACLE_HOME}/rdbms/admin/catproc.sql
set echo on

spool off

REM **************************************************************************
REM RUN PUPBLD.SQL FOR USER PROFILE
REM **************************************************************************
spool crdb4TESTDB.lst

connect system/manager
set echo off
@${ORACLE_HOME}/sqlplus/admin/pupbld.sql
set echo on

REM **************************************************************************
REM * ALTER SYS AND SYSTEM USERS
REM **************************************************************************
alter user sys temporary tablespace temp;
alter user system default tablespace tools temporary tablespace temp;
spool off

REM **************************************************************************
REM CREATE OPS$ORACLE ACCOUNT
REM **************************************************************************
spool crdb5TESTDB.lst
set echo on

connect internal
create user ops$oracle identified by scripts
default tablespace tools temporary tablespace temp;
grant connect, select any table to ops$oracle;

spool off