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.

Note Regarding Security Enhancements:

In this release of Oracle Database and in subsequent releases, several enhancements are being made to ensure the security of default database user accounts. You can find a security checklist for this release in Oracle Database Security Guide.Oracle Corporation recommends that you read this checklist and configure your database accordingly.

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, then 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 CLUSTER_DATABASE initialization parameter and opens it, making it available for normal use. You can then create tablespaces for the database.

See Also:

ALTER DATABASE for information on modifying a database
Oracle Database Java Developer's Guide for information on creating an Oracle Java virtual machine
CREATE TABLESPACE for information on creating tablespaces

Prerequisites

To create a database, you must have the SYSDBA system privilege.

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to EXCLUSIVE, then Oracle Database 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:

Oracle Database Reference for more information about the REMOTE_LOGIN_PASSWORDFILE parameter

Syntax

create_database::=
Description of create_database.gif follows

Description of the illustration create_database.gif

(database_logging_clauses::=, tablespace_clauses::=, set_time_zone_clause ::=)

database_logging_clauses::=
Description of database_logging_clauses.gif follows

Description of the illustration database_logging_clauses.gif

(file_specification::=)

tablespace_clauses::=
Description of tablespace_clauses.gif follows

Description of the illustration tablespace_clauses.gif

(file_specification::=, default_tablespace ::=, default_temp_tablespace ::=, undo_tablespace ::=)

default_tablespace ::=
Description of default_tablespace.gif follows

Description of the illustration default_tablespace.gif

default_temp_tablespace ::=
Description of default_temp_tablespace.gif follows

Description of the illustration default_temp_tablespace.gif

(file_specification::=)

extent_management_clause::=
Description of extent_management_clause.gif follows

Description of the illustration extent_management_clause.gif

size_clause::=
Description of size_clause.gif follows

Description of the illustration size_clause.gif

undo_tablespace ::=
Description of undo_tablespace.gif follows

Description of the illustration undo_tablespace.gif

(file_specification::=)

set_time_zone_clause ::=
Description of set_time_zone_clause.gif follows

Description of the illustration set_time_zone_clause.gif

Semantics

database

Specify the name of the database to be created. The name must match the value of the DB_NAME initialization parameter. The name can be up to 8 bytes long and can contain only ASCII characters. Oracle Database writes this name into the control file. If you subsequently issue an ALTER DATABASE statement that explicitly specifies a database name, Oracle Database 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, then Oracle Database uses the name specified by the initialization parameter DB_NAME. The DB_NAME initialization parameter must be set in the database initialization parameter file, and if you specify a different name from the value of that parameter, then the database returns an error. Please refer to "Schema Object Naming Guidelines " for additional rules to which database names should adhere.

USER SYS ..., USER SYSTEM ...

Use these clauses to establish passwords for the SYS and SYSTEM users. These clauses are not mandatory in this release. However, if you specify either clause, you must specify both clauses.

If you do not specify these clauses, then Oracle Database creates default passwords change_on_install for user SYS and manager for user SYSTEM. You can subsequently change these passwords using the ALTER USER statement. You can also use ALTER USER to add password management attributes after database creation.

See Also:

ALTER USER

CONTROLFILE REUSE Clause

Specify CONTROLFILE REUSE to reuse existing control files identified by the initialization parameter CONTROL_FILES, 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. When you create a database for the first time, Oracle Database creates a control file in the default destination, which is dependent on the value or several initialization parameters. See CREATE CONTROLFILE, "Semantics".

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, then the database returns an error.

MAXDATAFILES Clause

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 Database 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 Clause

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.

CHARACTER SET Clause

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 on CHARACTER SET

You cannot specify the AL16UTF16 character set as the database character set.

See Also:

Oracle Database Globalization Support Guide for more information about choosing a character set

NATIONAL CHARACTER SET Clause

Specify the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. Valid values are AL16UTF16 and UTF8. The default is AL16UTF16.

See Also:

Oracle Database Globalization Support Guide for information on Unicode datatype support.

database_logging_clauses

Use the database_logging_clauses to determine how Oracle Database will handle redo log files for this database.

LOGFILE Clause

Specify one or more files to be used as redo log files. Use the redo_log_file_spec form of file_specification to create regular redo log files in an operating system file system or to create Automatic Storage Management disk group redo log files. When using a form of ASM_filename, you cannot specify the autoextend_clause of redo_log_file_spec.

The redo_log_file_spec clause 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:

file_specification for a full description of this clause

If you omit the LOGFILE clause, then Oracle Database creates an Oracle-managed log file member in the default destination, which is one of the following locations (in order of precedence):

If either the DB_CREATE_ONLINE_LOG_DEST_n is set, then the database creates a log file member in each directory specified, up to the value of the MAXLOGMEMBERS initialization parameter.
If the DB_CREATE_ONLINE_LOG_DEST_n parameter is not set, but both the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initialization parameters are set, then the database creates one Oracle-managed log file member in each of those locations. The log file in the DB_CREATE_FILE_DEST destination is the first member.
If only the DB_CREATE_FILE_DEST initialization parameter is specified, then Oracle Database creates a log file member in that location.
If only the DB_RECOVERY_FILE_DEST initialization parameter is specified, then Oracle Database creates a log file member in that location.

In all these cases, the parameter settings must correctly specify operating system filenames or creation form Automatic Storage Management filenames, as appropriate.

If no values are set for any of these parameters, then the database creates a log file in the default location for the operating system on which the database is running. This log file is not an Oracle-managed file.

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, then Oracle Database generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance view V$LOG.

MAXLOGFILES Clause

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

MAXLOGMEMBERS Clause

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

MAXLOGHISTORY Clause

This parameter is useful only if you are using Oracle Database in archivelog mode with Real Application Clusters. Specify the maximum number of archived redo log files for automatic media recovery of Real Application Clusters. the database uses this value to determine how much space to allocate in the control file 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.

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.

FORCE LOGGING

Use this clause to put the database into FORCE LOGGING mode. Oracle Database will log all changes in the database except for changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING settings you specify for individual database objects.

FORCE LOGGING mode is persistent across instances of the database. That is, if you shut down and restart the database, the database is still in FORCE LOGGING mode. However, if you re-create the control file, Oracle Database will take the database out of FORCE LOGGING mode unless you specify FORCE LOGGING in the CREATE CONTROLFILE statement.

Note:

FORCE LOGGING mode can have performance effects. Please refer to Oracle Database Administrator's Guide for information on when to use this setting.

See Also:

CREATE CONTROLFILE

tablespace_clauses

Use the tablespace clauses to configure the SYSTEM and SYSAUX tablespaces and to specify a default temporary tablespace and an undo tablespace.

EXTENT MANAGEMENT LOCAL

Use this clause to create a locally managed SYSTEM tablespace. If you omit this clause, the SYSTEM tablespace will be dictionary managed.

Caution:

Once you create a locally managed SYSTEM tablespace, you cannot change it to be dictionary managed, nor can you create any other dictionary-managed tablespaces in this database.

If you specify this clause, the database must have a default temporary tablespace, because a locally managed SYSTEM tablespace cannot store temporary segments.

If you specify EXTENT MANAGEMENT LOCAL but you do not specify the DATAFILE clause, you can omit the default_temp_tablespace clause. Oracle Database will create a default temporary tablespace called TEMP with one datafile of size 10M with autoextend disabled.
If you specify both EXTENT MANAGEMENT LOCAL and the DATAFILE clause, then you must also specify the default_temp_tablespace clause and explicitly specify a datafile for that tablespace.

If you have opened the instance in Automatic Undo Management mode, similar requirements exist for the database undo tablespace:

If you specify EXTENT MANAGEMENT LOCAL but you do not specify the DATAFILE clause, you can omit the undo_tablespace clause. Oracle Database will create an undo tablespace named SYS_UNDOTBS.
If you specify both EXTENT MANAGEMENT LOCAL and the DATAFILE clause, then you must also specify the undo_tablespace clause and explicitly specify a datafile for that tablespace.

See Also:
Oracle Database Administrator's Guide for more information on locally managed and dictionary-managed tablespaces

SET DEFAULT TABLESPACE Clause

Use this clause to determine the default type of subsequently created tablespaces and of the SYSTEM and SYSAUX tablespaces. Specify either BIGFILE or SMALLFILE to set the default type of subsequently created tablespaces as a bigfile or smallfile tablespace, respectively.

A bigfile tablespace contains only one datafile or tempfile, which can contain up to 2³² or 4G blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.
A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to 2²² or 4M blocks.

If you omit this clause, then Oracle Database creates smallfile tablespaces by default.

See Also:

Oracle Database Administrator's Guide for more information about bigfile tablespaces
"Setting the Default Type of Tablespaces: Example" for an example using this syntax

SYSAUX Clause

Oracle Database creates both the SYSTEM and SYSAUX tablespaces as part of every database. Use this clause if you are not using Oracle-managed files and you want to specify one or more datafiles for the SYSAUX tablespace.

You must specify this clause if you have specified one or more datafiles for the SYSTEM tablespace using the DATAFILE clause. If you are using Oracle-managed files and you omit this clause, then the database creates the SYSAUX datafiles in the default location set up for Oracle-managed files.

If you have enabled Oracle-managed files and you omit the SYSAUX clause, then the database creates the SYSAUX tablespace as an online, permanent, locally managed tablespace with one datafile of 100 MB, with logging enabled and automatic segment-space management.

The syntax for specifying datafiles for the SYSAUX tablespace is the same as that for specifying datafiles during tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device.

See Also:

CREATE TABLESPACE for information on creating the SYSAUX tablespace during database upgrade and for information on specifying datafiles in a tablespace
Oracle Database Administrator's Guide for more information on creating the SYSAUX tablespace

default_tablespace

Specify this clause to create a default permanent tablespace for the database. Oracle Database creates a smallfile tablespace and subsequently will assign to this tablespace any non-SYSTEM users for whom you do not specify a different permanent tablespace. If you do not specify this clause, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.

The DATAFILE clause and extent_management_clause have the same semantics they have in a CREATE TABLESPACE statement. Please refer to "DATAFILE | TEMPFILE Clause " and extent_management_clause for information on these clauses.

default_temp_tablespace

Specify this clause to create a default temporary tablespace for the database. Oracle Database will assign to this temporary tablespace any users for whom you do not specify a different temporary tablespace. If you do not specify this clause, and if the database does not create a default temporary tablespace automatically in the process of creating a locally managed SYSTEM tablespace, then the SYSTEM tablespace is the default temporary tablespace.

Specify BIGFILE or SMALLFILE to determine whether the default temporary tablespace is a bigfile or smallfile tablespace. These clauses have the same semantics as in the "SET DEFAULT TABLESPACE Clause ".

The TEMPFILE clause part of this clause is optional if you have enabled Oracle-managed files by setting the DB_CREATE_FILE_DEST initialization parameter. If you have not specified a value for this parameter, then the TEMPFILE clause is required. If you have specified BIGFILE, then you can specify only one tempfile in this clause.

The syntax for specifying tempfiles for the default temporary tablespace is the same as that for specifying tempfiles during temporary tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device.

See Also:

CREATE TABLESPACE for information on specifying tempfiles

Note:

On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation.

Restrictions on Default Temporary Tablespaces

You cannot specify the SYSTEM tablespace in this clause.
The default temporary tablespace must have a standard block size.

The extent_management_clause clause has the same semantics in CREATE DATABASE and CREATE TABLESPACE statements. For complete information, please refer to the CREATE TABLESPACE ... extent_management_clause .

undo_tablespace

If you have opened the instance in automatic undo mode (that is, the UNDO_MANAGEMENT initialization parameter is set to AUTO), then you can specify the undo_tablespace to create a tablespace to be used for undo data. Oracle strongly recommends that you use automatic undo mode. However, if you want undo space management to be handled by way of rollback segments, then you must omit this clause. You can also omit this clause if you have set a value for the UNDO_TABLESPACE initialization parameter. If that parameter has been set, and if you specify this clause, then tablespace must be the same as that parameter value.

Specify BIGFILE if you want the undo tablespace to be a bigfile tablespace. A bigfile tablespace contains only one datafile, which can be up to 8 exabytes (8 million terabytes) in size.
Specify SMALLFILE if you want the undo tablespace to be a smallfile tablespace. A smallfile tablespace is a traditional Oracle Database tablespace, which can contain 1022 datafiles, each of which can be up to 4 MB in size.
The DATAFILE clause part of this clause is optional if you have enabled Oracle-managed files by setting the DB_CREATE_FILE_DEST initialization parameter. If you have not specified a value for this parameter, then the DATAFILE clause is required. If you have specified BIGFILE, then you can specify only one datafile in this clause.

The syntax for specifying datafiles for the undo tablespace is the same as that for specifying datafiles during tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device.

See Also:

CREATE TABLESPACE for information on specifying datafiles

If you specify this clause, then Oracle Database creates an undo tablespace named tablespace, creates the specified datafile(s) as part of the undo tablespace, and assigns this tablespace as the undo tablespace of the instance. Oracle Database will manage undo data using this undo tablespace. The DATAFILE clause of this clause has the same behavior as described in "DATAFILE Clause ".

If you have specified a value for the UNDO_TABLESPACE initialization parameter in your initialization parameter file before mounting the database, then you must specify the same name in this clause. If these names differ, then Oracle Database will return an error when you open the database.

If you omit this clause, then Oracle Database creates a default database with a default smallfile undo tablespace named SYS_UNDOTBS and assigns this default tablespace as the undo tablespace of the instance. This undo tablespace allocates disk space from the default files used by the CREATE DATABASE statement, and it has an initial extent of 10M. Oracle Database handles the system-generated datafile as described in "DATAFILE Clause ". If Oracle Database is unable to create the undo tablespace, then the entire CREATE DATABASE operation fails.

See Also:

Oracle Database Administrator's Guide for information on Automatic Undo Management and undo tablespaces
CREATE TABLESPACE for information on creating an undo tablespace after database creation

DATAFILE Clause

Specify one or more files to be used as datafiles. All these files become part of the SYSTEM tablespace. Use the datafile_tempfile_spec form of file_specification to create regular datafiles and tempfiles in an operating system file system or to create Automatic Storage Management disk group files.

Caution:

This clause is optional, as is the DATAFILE clause of the undo_tablespace clause. Therefore, to avoid ambiguity, if your intention is to specify a datafile for the SYSTEM tablespace with this clause, then do not specify it immediately after an undo_tablespace clause that does not include the optional DATAFILE clause. If you do so, Oracle Database will interpret the DATAFILE clause to be part of the undo_tablespace clause.

The syntax for specifying datafiles for the SYSTEM tablespace is the same as that for specifying datafiles during tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device.

See Also:

CREATE TABLESPACE for information on specifying datafiles

If you are running the database in automatic undo mode and you specify a datafile name for the SYSTEM tablespace, then Oracle Database expects to generate datafiles for all tablespaces. Oracle Database does this automatically if you are using Oracle-managed files—that is, you have set a value for the DB_CREATE_FILE_DEST initialization parameter. However, if you are not using Oracle-managed files and you specify this clause, then you must also specify the undo_tablespace clause and the default_temp_tablespace clause.

If you omit this clause, then:

If the DB_CREATE_FILE_DEST initialization parameter is set, then Oracle Database creates a 100 MB Oracle-managed datafile with a system-generated name in the default file destination specified in the parameter.
If the DB_CREATE_FILE_DEST initialization parameter is not set, then Oracle Database creates one datafile whose name and size depend on your operating system.

See Also:
file_specification for syntax

set_time_zone_clause

Use the SET TIME_ZONE clause to set the time zone of the database. You can specify the time zone in two ways:

By specifying a displacement from UTC (Coordinated Universal Time—formerly Greenwich Mean Time). The valid range of hh:mm is -12:00 to +14:00.
By specifying a time zone region. To see a listing of valid region names, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view.

Note:
Oracle recommends that you set the database time zone to UTC (0:00). Doing so can improve performance, especially across databases, as no conversion of time zones will be required.

See Also:
Oracle Database Reference for information on the dynamic performance views

Oracle Database normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. If you do not specify the SET TIME_ZONE clause, then the database uses the operating system time zone of the server. If the operating system time zone is not a valid Oracle Database time zone, then the database time zone defaults to UTC.

Examples

Creating a Database: Example

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

CREATE DATABASE sample
   CONTROLFILE REUSE 
   LOGFILE
      GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K, 
      GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K 
   MAXLOGFILES 5 
   MAXLOGHISTORY 100 
   MAXDATAFILES 10 
   MAXINSTANCES 2 
   ARCHIVELOG 
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE  
      'disk1:df1.dbf' AUTOEXTEND ON,
      'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp_ts
   UNDO TABLESPACE undo_ts 
   SET TIME_ZONE = '+02:00';

This example assumes that you have enabled Oracle-managed files by specifying a value for the DB_CREATE_FILE_DEST parameter in your initialization parameter file. Therefore no file specification is needed for the DEFAULT TEMPORARY TABLESPACE and UNDO TABLESPACE clauses.