Oracle7 Server SQL Reference

CREATE DATABASE

Purpose

To create a database, making it available for general use, with the following options:

to establish a maximum number of instances, data files , redo log files groups , or redo log file members

to specify names and sizes of data files and redo log files

to choose a mode of use for the redo log

Warning: This command prepares a database for initial use and erases any data currently in the specified files. Only use this command when you understand its ramifications.

Prerequisites

You must have the OSDBA role enabled.

If you are using Trusted Oracle7 and you plan to use the database in DBMS MAC mode, your operating system label should be the equivalent of DBLOW.

Syntax

Keyword and Parameters

database

is the name of the database to be created and can be up to eight bytes long. Oracle7 writes this name into the control file. If you subsequently issue an ALTER DATABASE statement and that explicitly specifies a database name, Oracle7 verifies that name with the name in the control file. Database names should adhere to the rules described in section, "Object Naming Rules," .

Note: You cannot use special characters from European or Asian character sets in a database name. For example, the umlaut is not allowed.

The database cannot be a Server Manager reserved word as documented in the Oracle Server Manager Manual. If you omit the database name from a CREATE DATABASE statement, the name specified by the initialization parameter DB_NAME is used.

CONTROLFILE REUSE

reuses existing control files identified by the initialization parameter CONTROL_FILES , thus ignoring and overwriting any information they currently contain. This option is usually used only when you are recreating a database, rather than creating one for the first time. You cannot use this option if you also specify a parameter value that requires that the control file be larger than the existing files. These parameters are MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

If you omit this option and any of the files specified by CONTROL_FILES already exist, Oracle7 returns an error message.

LOGFILE

specifies one or more files to be used as redo log files. Each filespec specifies a redo log file group containing one or more redo log file members, or copies. See the syntax description of filespec . All redo log files specified in a CREATE DATABASE statement are added to redo log thread number 1.

You can also choose the value of the GROUP parameter for the redo log file group. Each value uniquely identifies a redo log file group and can range from 1 to the value of the MAXLOGFILES parameter. You cannot specify multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle7 generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance table V$LOG .

If you omit the LOGFILE clause, Oracle7 creates two redo log file groups by default. The names and sizes of the default files vary depending on your operating system.

MAXLOGFILES

specifies the maximum number of redo log file groups that can ever be created for the database. Oracle7 uses this value to determine how much space in the control file to allocate for the names of redo log files. The default, minimum, and maximum values vary depending on your operating system.

The number of redo log file groups accessible to your instance is also limited by the initialization parameter LOG_FILES .

MAXLOGMEMBERS

specifies the maximum number of members, or copies, for a redo log file group. Oracle7 uses this value to determine how much space in the control file to allocate for the names of redo log files. The minimum value is 1. The maximum and default values vary depending on your operating system.

MAXLOGHISTORY

specifies the maximum number of archived redo log files for automatic media recovery of Oracle7 with the Parallel Server option. Oracle7 uses this value to determine how much space in the control file to allocate for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and varies depending on your operating system. The maximum value is limited only by the maximum size of the control file. Note that this parameter is only useful if you are using the Oracle7 with the Parallel Server option in parallel mode and archivelog mode.

MAXDATAFILES

specifies the maximum number of data files that can ever be created for the database.

The minimum value is 1. The maximum and default values depend on your operating system. The number of data files accessible to your instance is also limited by the initialization parameter

DB_FILES .

MAXINSTANCES

specifies the maximum number of instances that can simultaneously have this database mounted and open. This value takes precedence over the

value of the initialization parameter

INSTANCES . The minimum value is 1. The maximum and default values depend on your operating system.

ARCHIVELOG

establishes archivelog mode for redo log file groups. In this mode, the contents of a redo log file group must be archived before the group can be reused. This option prepares for the possibility of media recovery.

NOARCHIVELOG

establishes noarchivelog mode for redo log files groups. In this mode, the contents of a redo log file group need not be archived before the group can be reused. This option does not prepares for the possibility of media recovery.

The default is noarchivelog mode. After creating the database, you can change between archivelog mode and noarchivelog mode with the ALTER DATABASE command.

EXCLUSIVE

mounts the database in exclusive mode after it is created. This mode allows only your instance to access the database. Oracle7 automatically mounts the database in exclusive mode after creating it, so this keyword is entirely optional.

For multiple instances to access the database, you must first create the database, close and dismount the database, and then mount it in parallel mode. For information on closing, dismounting, and mounting the database, see the ALTER DATABASE command .

CHARACTER SET

specifies the character set the database uses to store data. You cannot change the database character set after creating the database. The supported character sets and default value of this parameter depends on your operating system.

DATAFILE

specifies one or more files to be used as data files. See the syntax description of filespec . These files all become part of the SYSTEM tablespace. If you omit this clause, Oracle7 creates one data file by default. The name and size of this default file depends on your operating system.

AUTOEXTEND

enables or disables the automatic extension of a datafile.

OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in ALTER DATABASE AUTOEXTEND or ALTER TABLESPACE AUTOEXTEND commands.

ON enable autoextend.

NEXT the size in bytes of the next increment of disk space to be automatically allocated to the datafile when more extents are required. You can also use K or M to specify this size in kilobytes or megabytes. The default is one data block.

MAXSIZE maximum disk space allowed for automatic extension of the datafile.

UNLIMITED set no limit on allocating disk space to the datafile.

Usage Notes

This command erases all data in any specified data files that already exist to prepare them for initial database use. If you use the command on an existing database, all data in the data files is lost.

After creating the database, this command mounts it in exclusive mode and opens it, making it available for normal use.

If you create a database using Trusted Oracle7, it is labeled with your operating system label and is created in OS MAC mode. If you plan to use the database in DBMS MAC mode, be sure you set values for DBHIGH and DBLOW. For more information on creating Trusted Oracle7 databases, see Trusted Oracle7 Server Administrator's Guide.

Example

The following statement creates a small database using defaults for all arguments:

CREATE DATABASE

The following statement creates a database and fully specifies each argument: