Have an opportunity to review your creation statements for accuracy before executing them

Automate the actual database creation process

Be able to create a log file with a record of the statements executed

Document the exact method used to create the database

Be able to recreate the database in the future, if required

Create the database, specifying the filename and size for the SYSTEM tablespace. The SYSTEM tablespace does not usually need to be very big—50 to 80 megabytes will usually do—and it should be on a different disk from other datafiles, if possible. You will need to define the names, locations, and sizes of the redo log files. You should define at least three redo log files, and they should be sized properly. See Chapter 11, for more information on redo log files.

Create the data dictionary views using the Oracle-supplied script catalog.sql. This script is normally found in the rdbms/admin directory below ORACLE_HOME.

Create the objects required by Oracle’s procedural components (i.e., PL/SQL) using the Oracle-supplied script catproc.sql. This script is normally found in the rdbms/admin directory below ORACLE_HOME.

Create a rollback segment in the SYSTEM tablespace, and place it online. A rollback segment is required in order to create any additional tablespaces. This rollback segment can be taken offline and removed later, if desired.

Create a rollback tablespace. Rollback segments should be created in a tablespace created for this purpose. Make the tablespace large enough to hold all of your rollback segments (allow space for growth). Ideally, the file for the rollback tablespace will reside on a disk separate from other database files, in order to avoid I/O contention. We recommend that this tablespace be called ROLLBACK and that the default storage be defined with equal-size INITIAL and NEXT extents (remember that rollback segments must have at least two extents).

Create one or more rollback segments. Specify a size that will accommodate your expected transaction load, and consider using the OPTIMAL parameter to allow rollback segments to shrink back to a predetermined size. Be sure to add the names of the rollback segments to the INIT.ORA file before the next database startup; otherwise, they will not be used.

Create a TEMPORARY tablespace. By default, Oracle will use the SYSTEM tablespace as the temporary tablespace for each user. This should be avoided, since performance will be negatively impacted. Create a separate tablespace for temporary segments. We recommend that this tablespace be called TEMP.

Create any additional tablespaces required. You will probably want to create one or more tablespaces for tables and one or more for indexes. Ideally, the data and index tablespaces should be on different disks in order to avoid I/O contention.

Run any other required Oracle-supplied scripts. These scripts will be located in the same directory as the catalog.sql script and will be used to create data and objects to support the particular set of Oracle features installed.

Modify the SYSTEM account to set default and temporary tablespaces. By default, both the DEFAULT tablespace and TEMPORARY tablespace are set to SYSTEM for this account, and you will not want to create any new objects there. You should also change the TEMPORARY tablespace for the SYS account, since it will be set to SYSTEM by default.