<popping my head out the window...>
Here is the create script I just did for 9i
[createdb.sql]
spool <DB_DESTINATION>\admin\<INSTANCE>\create\CreateDB.log
set echo on
connect SYS/<PASSWORD> as SYSDBA
startup nomount
pfile="<DB_DESTINATION>\admin\<INSTANCE>\pfile\init<INSTANCE>.ora";
-- build the db
CREATE DATABASE <INSTANCE>
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 64
MAXLOGMEMBERS 3
MAXDATAFILES 100
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_system.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGFILE GROUP 1
('<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_redo01.log') SIZE 2M,
GROUP 2 ('<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_redo02.log')
SIZE 2M,
GROUP 3 ('<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_redo03.log')
SIZE 2M;
spool off
-- create db files
spool <DB_DESTINATION>\admin\<INSTANCE>\create\CreateDBFiles.log
CREATE ROLLBACK SEGMENT BLD TABLESPACE SYSTEM
STORAGE(INITIAL 128K NEXT 128K MINEXTENTS 2 MAXEXTENTS 32);
ALTER ROLLBACK SEGMENT BLD ONLINE;
commit ;
-- NOTE-- SET THE SIZES OF THE
TABLESPACES BELOW TO MATCH YOUR DESIRED DATABASE
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_TEMP01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-- NOTE using the undo tablespace is a new feature of 9i and can
replace rollback segments.
-- The corresponding setting in the init<INSTANCE>.ora file
undo_management=manual or auto must be used. For the time being we are
using conventional rollback segments
-- UNDO TABLESPACE "UNDOTBS1" DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_undotbs01.dbf' SIZE 100M
REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
-- NOTE the commented CREATE TABLESPACE sections below are the 8i
conventional method of size management.
-- The uncommented sections use the new feature of 9i "EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO "
-- more info
http://www.asia.cnet.com/builder/architect/db/0,39009328,39106483,00.htm
-- and http://otn.oracle.com/products/oracle9i/daily/Aug01.html
-- CREATE TABLESPACE "USERS" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_users01.dbf' SIZE 25M
REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED ;
CREATE TABLESPACE "USERS" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_USERS01.dbf' SIZE 100M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;
ALTER TABLESPACE USERS ONLINE;
--CREATE TABLESPACE "TABLES" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_tables1.dbf' SIZE 100M
REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED ;
--ALTER TABLESPACE TABLES ONLINE;
CREATE TABLESPACE "TABLES" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_TABLES01.dbf' SIZE 100M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;
ALTER TABLESPACE TABLES ONLINE;
--CREATE TABLESPACE "INDXS" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_indx01.dbf' SIZE 25M
REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED ;
--ALTER TABLESPACE INDXS ONLINE;
CREATE TABLESPACE "INDXS" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_INDXS01.dbf' SIZE 50M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;
ALTER TABLESPACE INDXS ONLINE;
--CREATE TABLESPACE "REPORT" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_REPORT01.dbf' SIZE 10M
REUSE AUTOEXTEND ON NEXT 320K -MAXSIZE UNLIMITED ;
--ALTER TABLESPACE REPORT ONLINE;
CREATE TABLESPACE "REPORT" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_REPORT01.dbf' SIZE 1M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;
ALTER TABLESPACE REPORT ONLINE;
--CREATE TABLESPACE "XMLDB" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_XMLdb01.dbf' SIZE 20M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED ;
--ALTER TABLESPACE XMLDB ONLINE;
CREATE TABLESPACE "XMLDB" LOGGING DATAFILE
'<DB_DESTINATION>\oradata\<INSTANCE>\<INSTANCE>_XMLDB01.dbf' SIZE 5M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO ;
ALTER TABLESPACE XMLDB ONLINE;
CREATE TABLESPACE "ROLLBACK_SEGS" DATAFILE
'<DB_DESTINATION>\Oradata\<INSTANCE>\<INSTANCE>_ROLLBACK.DBF' SIZE 25M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED ;
ALTER TABLESPACE ROLLBACK_SEGS ONLINE;
spool off
-- create users
spool <DB_DESTINATION>\admin\<INSTANCE>\create\createUsers.log
alter user system temporary tablespace temp;
alter user system default tablespace users;
alter user system identified by <PASSWORD>;
alter user sys identified by <PASSWORD>;
Create users and add permissions here...
-- Create 8 rollback segments. Allows about 32 concurrent users with
open
-- transactions updating the database.
spool <DB_DESTINATION>\admin\<INSTANCE>\create\CreateRBSegs.log
CREATE ROLLBACK SEGMENT RS_01A
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_01B
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_02A
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_02B
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_03A
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_03B
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_04A
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_04B
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 128K
NEXT 128K
OPTIMAL 2M
MINEXTENTS 2
MAXEXTENTS 32);
CREATE ROLLBACK SEGMENT RS_LRG
TABLESPACE ROLLBACK_SEGS
STORAGE(
INITIAL 10M
NEXT 10M
OPTIMAL 20M
MINEXTENTS 2
MAXEXTENTS 32);
ALTER ROLLBACK SEGMENT "RS_01A" ONLINE;
ALTER ROLLBACK SEGMENT "RS_01B" ONLINE;
ALTER ROLLBACK SEGMENT "RS_02A" ONLINE;
ALTER ROLLBACK SEGMENT "RS_02B" ONLINE;
ALTER ROLLBACK SEGMENT "RS_03A" ONLINE;
ALTER ROLLBACK SEGMENT "RS_03B" ONLINE;
ALTER ROLLBACK SEGMENT "RS_04A" ONLINE;
ALTER ROLLBACK SEGMENT "RS_04B" ONLINE;
ALTER ROLLBACK SEGMENT "RS_LRG" ONLINE;
spool off
-- Create catalog area
spool <DB_DESTINATION>\admin\<INSTANCE>\create\CreateDBCatalog.log
set echo on
alter user sys temporary tablespace TEMP;
@<ORACLE_HOME>\rdbms\admin\catalog.sql;
@<ORACLE_HOME>\rdbms\admin\catexp7.sql;
@<ORACLE_HOME>\rdbms\admin\catblock.sql;
@<ORACLE_HOME>\rdbms\admin\catproc.sql;
@<ORACLE_HOME>\rdbms\admin\catoctk.sql;
@<ORACLE_HOME>\rdbms\admin\owminst.plb;
connect SYSTEM/<PASSWORD>
@<ORACLE_HOME>\sqlplus\admin\pupbld.sql;
spool off
connect SYSTEM/<PASSWORD>
set echo on
spool <DB_DESTINATION>\admin\<INSTANCE>\create\sqlPlusHelp.log
@<ORACLE_HOME>\sqlplus\admin\help\hlpbld.sql helpus.sql;
spool off
-- post db build
spool <DB_DESTINATION>\admin\<INSTANCE>\create\postDBCreation.log
connect SYS/<PASSWORD> as SYSDBA
@<ORACLE_HOME>\rdbms\admin\utlrp.sql;
shutdown ;
startup mount
pfile="<DB_DESTINATION>\admin\<INSTANCE>\pfile\init<INSTANCE>.ora";
alter database archivelog;
alter database open;
alter system archive log start;
shutdown ;
connect SYS/<PASSWORD> as SYSDBA
set echo on
spool <DB_DESTINATION>\admin\<INSTANCE>\create\postDBCreation.log
create spfile='<ORACLE_HOME>\database\spfile<INSTANCE>.ora' FROM
pfile='<DB_DESTINATION>\admin\<INSTANCE>\pfile\init<INSTANCE>.ora';
spool off
prompt If there arent any errors press a key to continue, this will take
you to the batch job check the logs...
pause
Exit
Hth
bob
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
