Create a Database Instance in Oracle

Before 8i there was no easy way to get the scripts to create databases in Oracle. Like it was a state secret or something. Larry, you are a moron.

Oracle seems to have a lack of basic utilities, like schema extraction, etc. Some of this DDL can be seen in a "full" database export, but gee whiz, Oracle seems to revel in making it difficult to use their expensively licensed product.

#################### cut here ##########################
REM * Database instance create script used with Oracle 8.0.5
REM * Start the  instance (ORACLE_SID here must be set to ).
REM * Use svrmgrl so admin procedures get run correctly.
-- Shutdown instance, remove all datafiles, control files, lockfiles,
-- logfiles to rerun and recreate the same instance

set termout on
set echo on
spool create.log

connect internal
startup nomount pfile=/apps/oracle/product/8.0.5/dbs/initumnp.ora

create database "umnp"
    maxinstances 8
    maxlogfiles  32
    maxdatafiles 400
    character set "US7ASCII"
    datafile
	'/vol01/oradata/umnp/system01.dbf'	size  100M
    logfile 
	('/vol05/oradata/umnp/redo01.01',
	'/vol06/oradata/umnp/redo01.02') size 20M,
        group 2
        ('/vol05/oradata/umnp/redo02.01',
        '/vol06/oradata/umnp/redo02.02') size 20M,
        group 3
        ('/vol05/oradata/umnp/redo03.01',
        '/vol06/oradata/umnp/redo03.02') size 20M,
        group 4
        ('/vol05/oradata/umnp/redo04.01',
        '/vol06/oradata/umnp/redo04.02') size 20M,
        group 5
        ('/vol05/oradata/umnp/redo05.01',
        '/vol06/oradata/umnp/redo05.02') size 20M ;


@/apps/oracle/product/8.0.5/rdbms/admin/catalog.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catproc.sql
@/apps/oracle/product/8.0.5/rdbms/admin/catparr.sql


create rollback segment temprbs
   tablespace system
   storage (initial 25K next 25K minextents 2 maxextents 99);

alter rollback segment temprbs online;

drop tablespace rollback01;
create tablespace rollback01 
 datafile '/vol07/oradata/umnp/rollback01_01.dbf' size 128M reuse
  default storage (initial 1m next 1m maxextents unlimited pctincrease 0);

drop tablespace temp01 ;
create tablespace temp01 
 datafile '/vol01/oradata/umnp/temp01_01.dbf' size 128M reuse
  default storage (initial 2m next 2m maxextents unlimited pctincrease 0) 
  temporary; 

drop tablespace user01 ;
create tablespace user01 
 datafile '/vol02/oradata/umnp/user01_01.dbf' size 100M reuse
  default storage (initial 100k next 100k maxextents unlimited pctincrease 0); 

create rollback segment R01
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R02
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R03
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R04
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);

create rollback segment R05
 tablespace rollback01
 storage (initial 1M next 1M optimal 10M
          minextents 20 maxextents 250);


alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
alter rollback segment r04 online;
alter rollback segment r05 online;

alter rollback segment temprbs offline;

REM * Alter SYS and SYSTEM users, because Oracle will make SYSTEM
REM * the default and temporary tablespace by default, and we don't
REM * want that.
REM *
alter user sys temporary tablespace temp01;
alter user system default tablespace user01 temporary tablespace temp01;

REM * Now run the Oracle-supplied scripts we need for this DB
REM *
@/apps/oracle/product/8.0.5/rdbms/admin/catblock.sql 
@/apps/oracle/product/8.0.5/rdbms/admin/catio.sql 
@/apps/oracle/product/8.0.5/rdbms/admin/dbmspool.sql

REM * All done, so close the log file and exit.
REM *
spool off
exit

################## cut here ###################################

The views and opinions expressed in this page are strictly those of the page author.
The contents of this page have not been reviewed or approved by the University of Minnesota.