Manually create a database

Home

Toolkit:

My OraFAQ Blog

Contact me

Sample create scripts for manual creation of a database.

Use these scripts as a guideline to write your own manual database creation scripts. These scripts will work for Oracle 8i and above. In 9i and above, replace 'CONNECT INTERNAL' with 'CONNECT SYS AS SYSDBA' and run in sql*plus.

In the examples below, the oracle_home is c:\oracle\ora81\ .

NB:On Windows, you must create an Oracle service in addition to creating the database. This is a Windows-specific step that doesn't have to be carried out on other platforms. Go here for instructions on setting up the service. I find it easiest to create the service and start it up before attempting to create the database.

The first step is to create the database. Before you begin, your init.ora file must be in place. 

--LISTING 1

spool c:\oracle\ora81\database\create\creTRN3.out
set echo on
connect INTERNAL
startup nomount pfile=c:\oracle\ora81\database\initTRN3.ora

CREATE DATABASE ADM_TRN3
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 254
    MAXINSTANCES 1
    MAXLOGHISTORY 452
LOGFILE
  GROUP 1 (
    'K:\ORACLE\ORADATA\REDO\TRN3\LOGTRN3_1A.LGF',
    'F:\ORACLE\ORADATA\REDO\TRN3\LOGTRN3_1A.LGF'
  ) SIZE 20M reuse,
  GROUP 2 (
    'K:\ORACLE\ORADATA\REDO\TRN3\LOGTRN3_2A.LGF',
    'F:\ORACLE\ORADATA\REDO\TRN3\LOGTRN3_2A.LGF'
  ) SIZE 20M reuse,
  GROUP 3 (
    'K:\ORACLE\ORADATA\REDO\TRN3\LOGTRN3_3A.LGF',
    'F:\ORACLE\ORADATA\REDO\TRN3\LOGTRN3_3A.LGF'
  ) SIZE 20M reuse
DATAFILE
  'd:\ORACLE\ORADATA\SYSTEM\TRN3\SYSTEM1.DBF' size 400M reuse
CHARACTER SET WE8ISO8859P1
national character set WE8ISO8859P1
;
spool off

Now that you have created the database, you must load the data dictionary. 

--LISTING 2 

connect INTERNAL

alter database datafile 'd:\ORACLE\ORADATA\SYSTEM\TRN3\SYSTEM1.DBF' autoextend on;

@c:\oracle\ora81\rdbms\admin\catalog.sql
@c:\oracle\ora81\rdbms\admin\catproc.sql

commit;

The final step is to create the rollback segments and tablespaces. 

--LISTING 3

spool "c:\oracle\ora81\database\create scripts\trn3\trn3run2.out"
set echo on

connect internal

--create a system rollback segment

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

alter rollback segment sysroll online;

create tablespace rollback
datafile 'D:\ORACLE\ORADATA\RBACK\TRN3\rback1.dbf' size 300M reuse
AUTOEXTEND ON NEXT 4M MAXSIZE 1000M
default storage (
	initial 200K
	next 200K
	pctincrease 0
	minextents 2);

--Create the rollback segments you referred to in your init.ora

create rollback segment RBS01 tablespace ROLLBACK
storage (initial 200K next 200K
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS02 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS03 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS04 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS05 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS06 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS07 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS08 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 500 optimal 4M);

create rollback segment RBS09 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS10 tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 50 optimal 4M);

create rollback segment RBS_BULK tablespace ROLLBACK
storage (initial 200K next 200K 
	minextents 2 maxextents 500 optimal 10M);

alter rollback segment RBS01 online;
alter rollback segment RBS02 online;
alter rollback segment RBS03 online;
alter rollback segment RBS04 online;
alter rollback segment RBS05 online;
alter rollback segment RBS06 online;
alter rollback segment RBS07 online;
alter rollback segment RBS08 online;
alter rollback segment RBS09 online;
alter rollback segment RBS10 online;
alter rollback segment RBS_BULK online;

alter rollback segment sysroll offline;

-- create a temporary tablespace

create temporary tablespace TEMPORARY_DATA
tempfile 'K:\ORACLE\ORADATA\TEMP\TRN3\temporary_data.dbf' size 250M reuse
AUTOEXTEND ON MAXSIZE 500M
--this extent size should match your sort_area_size
extent managment local uniform size 1M; 

--create your data tablespaces

create tablespace ASP_DATA
datafile 'E:\ORACLE\ORADATA\DATA\TRN3\ASP_DATA1.DBF' size 500M reuse
autoextend on NEXT 4M maxsize 2000M
default storage (initial 100K next 100K minextents 1 
maxextents unlimited pctincrease 20);

create tablespace INDICES
datafile 'G:\ORACLE\ORADATA\INDEX\TRN3\INDICES1.DBF' size 400M reuse
autoextend on NEXT 4M maxsize 1000M
default storage (initial 500K next 500K minextents 1 
maxextents unlimited pctincrease 20);

create tablespace USER_DATA
datafile 'E:\ORACLE\ORADATA\DATA\TRN3\USER_DATA1.DBF' size 250M reuse
autoextend on NEXT 4M maxsize 500M
default storage (initial 500K next 500K minextents 1 
maxextents 500 pctincrease 50);

create tablespace TOOLS
datafile 'E:\ORACLE\ORADATA\DATA\TRN3\BULK_LOAD1.DBF' size 300M reuse
autoextend on NEXT 4M maxsize 500M
default storage (initial 200K next 200K minextents 1 
maxextents 1000 pctincrease 30);

--sys and system should use the temporary tablespace for temp data
--system should use tools tbsp. for its objects (default is SYSTEM tb, that's bad)

alter user sys temporary tablespace temporary_data;
alter user system default tablespace tools temporary tablespace temporary_data;

commit;

@c:\oracle\ora81\rdbms\admin\dbmspool.sql

connect system
@c:\oracle\ora81\rdbms\admin\catdbsyn.sql

commit;

spool off

Check the output files of your scripts after running them. Now that you've created the database, you can create users.

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...