CREATE DATABASE

Caution: This statement prepares a database for initial use and erases any data currently in the specified files. Use this statement only when you understand its ramifications.

Purpose

Use the CREATE DATABASE statement to create a database, making it available for general use.

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

After creating the database, this statement mounts it in either exclusive or parallel mode (depending on the value of the PARALLEL_SERVER initialization parameter) and opens it, making it available for normal use. You can then create tablespaces and rollback segments for the database.

See Also: ALTER DATABASE for information on modifying a database Oracle8i Java Developer's Guide for information on creating an Oracle8i Java virtual machine CREATE ROLLBACK SEGMENT and CREATE TABLESPACE for information on creating rollback segments and tablespaces

Prerequisites

You must have the OSDBA role enabled.

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to exclusive, Oracle returns an error when you attempt to re-create the database. To avoid this message, either set the parameter to shared, or re-create your password file before re-creating the database.

See Also: Oracle8i Reference for more information about the REMOTE_LOGIN_PASSWORDFILE parameter

Syntax

autoextend_clause::=

maxsize_clause::=

filespec: See filespec.

Keyword and Parameters

database

Specify the name of the database to be created and can be up to 8 bytes long. The database name can contain only ASCII characters. Oracle writes this name into the control file. If you subsequently issue an ALTER DATABASE statement that explicitly specifies a database name, Oracle verifies that name with the name in the control file.

Note: You cannot use special characters from European or Asian character sets in a database name. For example, characters with umlauts are not allowed.

If you omit the database name from a CREATE DATABASE statement, Oracle uses the name specified by the initialization parameter DB_NAME. If the DB_NAME initialization parameter has been set, and you specify a different name from the value of that parameter, Oracle returns an error.

See Also: "Schema Object Naming Guidelines" for additional rules to which database names should adhere

CONTROLFILE REUSE

Specify CONTROLFILE REUSE to reuse existing control files identified by the initialization parameter CONTROL_FILES, thus ignoring and overwriting any information they currently contain. Normally you use this clause only when you are re-creating a database, rather than creating one for the first time. You cannot use this clause 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 clause and any of the files specified by CONTROL_FILES already exist, Oracle returns an error.

LOGFILE filespec

Specify 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 (copies). All redo log files specified in a CREATE DATABASE statement are added to redo log thread number 1.

See Also: filespec for the syntax of filespec

GROUP integer

Specify the number that identifies the redo log file group. The value of integer can range from 1 to the value of the MAXLOGFILES parameter. A database must have at least two redo log file groups. You cannot specify multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle 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, Oracle creates two redo log file groups by default. The names and sizes of the default files depend on your operating system.

MAXLOGFILES integer

Specify the maximum number of redo log file groups that can ever be created for the database. Oracle 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 depend on your operating system.

MAXLOGMEMBERS integer

Specify the maximum number of members, or copies, for a redo log file group. Oracle 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 depend on your operating system.

MAXLOGHISTORY integer

Specify the maximum number of archived redo log files for automatic media recovery with Oracle Parallel Server. Oracle 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 depends on your operating system. The maximum value is limited only by the maximum size of the control file.

Note: This parameter is useful only if you are using Oracle with the Parallel Server option in parallel mode, and archivelog mode enabled.

MAXDATAFILES integer

Specify the initial sizing of the datafiles section of the control file at CREATE DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the Oracle control file to expand automatically so that the datafiles section can accommodate more files.

The number of datafiles accessible to your instance is also limited by the initialization parameter DB_FILES.

MAXINSTANCES integer

Specify the maximum number of instances that can simultaneously have this database mounted and open. This value takes precedence over the value of initialization parameter INSTANCES. The minimum value is 1. The maximum and default values depend on your operating system.

ARCHIVELOG | NOARCHIVELOG

ARCHIVELOG	Specify ARCHIVELOG if you want the contents of a redo log file group to be archived before the group can be reused. This clause prepares for the possibility of media recovery.
NOARCHIVELOG	Specify NOARCHIVELOG if the contents of a redo log file group need not be archived before the group can be reused. This clause does not allow 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 statement.

CHARACTER SET character_set

Specify the character set the database uses to store data. The supported character sets and default value of this parameter depend on your operating system.

Restriction: You cannot specify any fixed-width multibyte character sets as the database character set.

See Also: Oracle8i National Language Support Guide for more information about character sets

NATIONAL CHARACTER SET character_set

Specify the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. If not specified, the national character set defaults to the database character set.

See Also: Oracle8i National Language Support Guide for valid character set names

DATAFILE filespec

Specify one or more files to be used as datafiles. All these files become part of the SYSTEM tablespace. If you omit this clause, Oracle creates one datafile by default. The name and size of this default file depend on your operating system.

Note: Oracle recommends that the total initial space allocated for the SYSTEM tablespace be a minimum of 5 megabytes.

See Also: filespec for syntax

autoextend_clause

The autoextend_clause lets you enable or disable the automatic extension of a datafile. If you do not specify this clause, datafiles are not automatically extended.

OFF	Specify OFF to 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 statements.
ON	Specify ON to enable autoextend.
NEXT integer	Specify the size in bytes of the next increment of disk space to be allocated to the datafile automatically when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is the size of one data block.
MAXSIZE	Specify the maximum disk space allowed for automatic extension of the datafile: integer indicates the maximum disk space in bytes. Use K or M to specify this size in kilobytes or megabytes. UNLIMITED indicates that there is no limit on the allocation of disk space to the datafile.

Examples

CREATE DATABASE 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:

CREATE DATABASE newtest 

CONTROLFILE REUSE 
LOGFILE
   GROUP 1 ('diskb:log1.log', 'diskc:log1.log') SIZE 50K, 
   GROUP 2 ('diskb:log2.log', 'diskc:log2.log') SIZE 50K 
MAXLOGFILES 5 
MAXLOGHISTORY 100 
DATAFILE 'diska:dbone.dat' SIZE 2M 
MAXDATAFILES 10 
MAXINSTANCES 2 
ARCHIVELOG 
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET JA16SJISFIXED
DATAFILE  
'disk1:df1.dbf' AUTOEXTEND ON
'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;