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.
Now that you have created the database, you must load the data dictionary.
The final step is to create the rollback segments and tablespaces.
Check the output files of your scripts after running them. Now that you've created the database, you can create
users.
--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
--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;
--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
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...