set echo off

--
--  File     : create_DEMO.sql
--  Version  : 1.0 / 30.03.2001
--  Created  : Hans Wesnitzer / MuniQSoft GmbH
--
--  Purpose  : Creates a new database DEMO.
--
--  Requires : Oracle RDBMS 8 onwards and directories with OFA structure
--             You have to rename used SID DEMO and to create all directories first.
--
--  To Start : Must be called from svrmgrl or sqlplus.
--
--  Tested on: Oracle 8.1.6 - Sun Solaris 2.8
--
--  Changes  :
--

set echo on

SPOOL $ORACLE_BASE/admin/DEMO/create/crdb_DEMO.log

CONNECT internal
STARTUP NOMOUNT PFILE=$ORACLE_HOME/dbs/initDEMO.ora

--
-- Create SYSTEM tablespace and all REDOLOG Groups
-- 
-- ------------------------------------------------------

CREATE DATABASE "DEMO"
   maxinstances   8
   maxlogmembers  5
   maxlogfiles   32
   maxdatafiles 600
   character set WE8ISO8859P1
   national character set WE8ISO8859P1
DATAFILE 
   '$ORACLE_BASE/oradata/DEMO/system01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGFILE
   GROUP 1 
   ( 
      '$ORACLE_BASE/oradata/DEMO/redo10.log',
      '$ORACLE_BASE/oradata/DEMO/redo11.log'
   ) SIZE 10M,
   GROUP 2 
   ( 
      '$ORACLE_BASE/oradata/DEMO/redo20.log',
      '$ORACLE_BASE/oradata/DEMO/redo21.log'
   ) SIZE 10M,
   GROUP 3
   ( 
      '$ORACLE_BASE/oradata/DEMO/redo30.log',
      '$ORACLE_BASE/oradata/DEMO/redo31.log'
   ) SIZE 10M,
   GROUP 4
   ( 
      '$ORACLE_BASE/oradata/DEMO/redo40.log',
      '$ORACLE_BASE/oradata/DEMO/redo41.log'
   ) SIZE 10M
;

--
-- Create temporary RBS rbs_sys
-- Has to be deleted after execution of first statements
--
-- ------------------------------------------------------

CREATE ROLLBACK SEGMENT rbs_sys TABLESPACE system
  STORAGE
  (INITIAL     128K 
   NEXT        128K
   MINEXTENTS  2
   MAXEXTENTS  200);
ALTER ROLLBACK SEGMENT rbs_sys ONLINE;

--    
-- Create tablespace rbs for rollback segments
--
-- Do not set PCTINCREASE for rollback segment tablespace
-- nor for any rollback segment. Rollback segments always
-- have a PCTINCREASE value of 0
--
-- ------------------------------------------------------

CREATE TABLESPACE rbs
   DATAFILE 
      '$ORACLE_BASE/oradata/DEMO/rbs01.dbf' SIZE 100M
   DEFAULT STORAGE 
   ( 
      INITIAL    128K 
      NEXT       128K
      MINEXTENTS 20
      MAXEXTENTS UNLIMITED
   )
   PERMANENT
   ONLINE;
   

CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs01 ONLINE;

CREATE ROLLBACK SEGMENT rbs02 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs02 ONLINE;

CREATE ROLLBACK SEGMENT rbs03 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs03 ONLINE;

CREATE ROLLBACK SEGMENT rbs04 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs04 ONLINE;

CREATE ROLLBACK SEGMENT rbs05 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs05 ONLINE;

CREATE ROLLBACK SEGMENT rbs06 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs06 ONLINE;

CREATE ROLLBACK SEGMENT rbs07 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs07 ONLINE;

CREATE ROLLBACK SEGMENT rbs08 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs08 ONLINE;

CREATE ROLLBACK SEGMENT rbs09 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs09 ONLINE;

CREATE ROLLBACK SEGMENT rbs10 TABLESPACE rbs
  STORAGE
  (INITIAL     128K
   NEXT        128K
   MINEXTENTS  20
   MAXEXTENTS  UNLIMITED
   OPTIMAL     2560K);
ALTER ROLLBACK SEGMENT rbs10 ONLINE;

--
-- To activate rollback segments at database startup, they have to 
-- be set in the INIT.ORA file with parameter ROLLBACK_SEGMENTS.
-- The user created rollback segment in tablespace system can now 
-- be set offline.
--
-- ------------------------------------------------------

ALTER ROLLBACK SEGMENT rbs_sys OFFLINE;


--
-- Create temporary tablespace temp
--
-- INITIAL should be set to SORT_AREA_SIZE + 2*DB_BLOCK_SIZE
-- NEXT should be set to INITIAL
--
-- e.g. sort_area_size = 1048576  (1M)
--      db_block_size  = 4096	  (4K)
--      --> initial    = 1056768  (1048576 + 2*4096 = 1032K)
--
-- ------------------------------------------------------

CREATE TABLESPACE temp 
   DATAFILE 
      '$ORACLE_BASE/oradata/DEMO/temp01.dbf' SIZE 100M
   DEFAULT STORAGE 
   ( 
      INITIAL     1032K 
      NEXT        1032K 
      MINEXTENTS  1 
      PCTINCREASE 0
   ) 
   TEMPORARY
   ONLINE;

--
-- Create tablespace tools
--
-- ------------------------------------------------------

CREATE TABLESPACE tools 
   DATAFILE 
      '$ORACLE_BASE/oradata/DEMO/tools01.dbf' SIZE 50M 
   DEFAULT STORAGE 
   ( 
      INITIAL     128K 
      NEXT        128K 
      MINEXTENTS  1 
      MAXEXTENTS  4096 
      PCTINCREASE 0
   )
   PERMANENT
   ONLINE;

--
-- Create tablespace users 
--
-- ------------------------------------------------------

CREATE TABLESPACE users 
   DATAFILE 
      '$ORACLE_BASE/oradata/DEMO/users01.dbf' SIZE 100M 
   DEFAULT STORAGE 
   ( 
      INITIAL     128K 
      NEXT        128K 
      MINEXTENTS  1 
      MAXEXTENTS  4096 
      PCTINCREASE 0
   )
   PERMANENT
   ONLINE;

--
-- Create tablespace data 
--
-- ------------------------------------------------------

CREATE TABLESPACE data 
   DATAFILE 
      '$ORACLE_BASE/oradata/DEMO/data01.dbf' SIZE 100M
   DEFAULT STORAGE 
   ( 
      INITIAL     128K 
      NEXT        128K 
      MINEXTENTS  1 
      MAXEXTENTS  4096 
      PCTINCREASE 0
   )
   PERMANENT
   ONLINE;

--
-- Create  tablespace indx
--
-- ------------------------------------------------------

CREATE TABLESPACE indx
   DATAFILE 
      '$ORACLE_BASE/oradata/DEMO/indx01.dbf' SIZE 100M
   DEFAULT STORAGE 
   ( 
      INITIAL     128K 
      NEXT        128K 
      MINEXTENTS  1 
      MAXEXTENTS  4096 
      PCTINCREASE 0
   )
   PERMANENT
   ONLINE;


--
-- Create tablespace data_medium
--
-- ------------------------------------------------------

CREATE TABLESPACE data_medium
   DATAFILE
      '$ORACLE_BASE/oradata/DEMO/data_medium01.dbf' SIZE 100M
   DEFAULT STORAGE
   (
      INITIAL     4M
      NEXT        4M
      MINEXTENTS  1
      MAXEXTENTS  4096
      PCTINCREASE 0
   )
   PERMANENT
   ONLINE;


--
-- Create tablespace idx_medium
--
-- ------------------------------------------------------

CREATE TABLESPACE idx_medium
   DATAFILE
      '$ORACLE_BASE/oradata/DEMO/idx_medium01.dbf' SIZE 100M
   DEFAULT STORAGE
   (
      INITIAL     4M
      NEXT        4M
      MINEXTENTS  1
      MAXEXTENTS  4096
      PCTINCREASE 0
   )
   PERMANENT
   ONLINE;


--
-- Change some default values for administrative users
--
-- ------------------------------------------------------

ALTER USER sys
  TEMPORARY TABLESPACE temp;
ALTER USER sys IDENTIFIED BY manager;

ALTER USER system
  TEMPORARY TABLESPACE temp;
ALTER USER system IDENTIFIED BY manager;


--
-- Execute all necessary system scripts
--
-- ------------------------------------------------------

SET TERMOUT OFF;

-- Creates data dictionary views. This is the very first
-- script which must be executed during database generation
@$ORACLE_HOME/rdbms/admin/catalog.sql

-- Scripts for the PL/SQL procedural option. Must be executed.
@$ORACLE_HOME/rdbms/admin/catproc.sql


--
-- Execute some additional system scripts
--
-- ------------------------------------------------------

-- This package allows you to display the sizes of objects in the
-- shared pool, and mark them for keeping or unkeeping in order to
-- reduce memory fragmentation.
@$ORACLE_HOME/rdbms/admin/dbmspool.sql

-- Create views of oracle locks. 
-- To use with $ORACLE_HOME/rdbms/admin/utllockt.sql
@$ORACLE_HOME/rdbms/admin/catblock.sql

-- Creates the default table for storing the output
-- of the ANALYZE LIST CHAINED ROWS command
-- @$ORACLE_HOME/rdbms/admin/utlchain.sql

-- Creates the EXCEPTION table
-- @$ORACLE_HOME/rdbms/admin/utlexcpt.sql

-- Creates the default table for storing the output of the
-- analyze validate command on a partitioned table
-- @$ORACLE_HOME/rdbms/admin/utlvalid.sql


--
-- Change some default values for administrative users
--
-- ------------------------------------------------------

ALTER USER outln
  DEFAULT TABLESPACE tools
  TEMPORARY TABLESPACE temp;

ALTER USER dbsnmp
  DEFAULT TABLESPACE tools
  TEMPORARY TABLESPACE temp;


DISCONNECT;
CONNECT system/manager;

--
-- SQL*PLUS product user profile
--
-- ------------------------------------------------------

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

--
-- SQL*PLUS Help Tables
--
-- ------------------------------------------------------
-- Start Shell Script: ./create_help_tables.sh

EXIT;