Appendix: Oracle 10g Database Creation Scripts
22 Oracle Retail Data Warehouse
nls_sort = BINARY # Should be explicitly set to
ensure all sessions get the same order
nls_territory = AMERICA # Default
open_cursors = 900 # Oracle Retail required
(minimum=900); default is 50
optimizer_features_enable = 10.2.0.1
optimizer_mode = CHOOSE # Oracle Retail required
pga_aggregate_target = 100M
plsql_optimize_level = 2 # 10g change; use this setting
to optimize plsql performance
plsql_debug = false # 10g change; use this setting
to optimize plsql peformance
processes = 500 # Max number of OS processes
that can connect to the db
query_rewrite_enabled = TRUE # Oracle Retail required for
function-based indexes
session_cached_cursors = 900 # Oracle Retail required; 10g
uses to cache sql cursors in pl/sql
undo_management = AUTO
undo_retention = 1800 # Currently set for 30
minutes; set to avg length of transactions in sec
undo_tablespace = undo_ts
user_dump_dest = <admin_path>/udump
utl_file_dir = <utl_file_path>
workarea_size_policy = auto # Should be set to auto when
pga_aggregate_target is set
# *** Set these parameters for Oracle Retail Data Warehouse (RDW) database ***
#nls_date_format = DD-MON-RRRR # Required by
MicroStrategy
#query_rewrite_integrity = TRUSTED
#star_transformation_enabled = TRUE
#utl_file_dir = <Windows_utl_file_path>,
<UNIX_util_file_path>
# *** Archive Logging, set if needed ***
#log_archive_dest_1 = 'location=<admin_path>/arch/'
#log_archive_format = SIDarch_%r_%s_%t.log
#log_archive_max_processes = 1 # Default:1
#log_archive_min_succeed_dest = 1 # Default:1
#log_buffer = 262144 # Set to (512K or 128K)*CPUs
#log_checkpoint_interval = 51200 # Default:0 - unlimited
#log_checkpoint_timeout = 7200 # Default:1800 seconds
----------------------------------------------------------------------------------
--- Script: crdb1.sql
--- Execute as: sysdba
--- Note: Before running this script:
--- Modify <datafile_path> values.
--- Modify SID values.
--- Adjust sizes for redo logs, datafiles and tempfile
----------------------------------------------------------------------------------
spool crdb1.log
STARTUP NOMOUNT pfile=${ORACLE_HOME}/dbs/initSID.ora
CREATE DATABASE "SID"
MAXDATAFILES 1000
CHARACTER SET UTF8
DATAFILE
'<datafile_path>/system01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE
2000M
LOGFILE
GROUP 1 ('<datafile_path>/redo1a.log') SIZE 1000M,
GROUP 2 ('<datafile_path>/redo2a.log') SIZE 1000M,
