|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
CREATE CLUSTER to CREATE JAVA, 5 of 12
Note Regarding Security Enhancements:
In this release of Oracle9i and in subsequent releases, several enhancements are being made to ensure the security of default database user accounts.
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
CLUSTER_DATABASE initialization parameter) and opens it, making it available for normal use. You can then create tablespaces and rollback segments for the database.
To create a database, you must have the
SYSDBA system privilege.
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.
Oracle9i Database Reference for more information about the
filespec: See filespec.
Specify the name of the database to be created. The name 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
DATABASE statement that explicitly specifies a database name, Oracle verifies that name with the name in the control file.
If you omit the database name from a
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.
"Schema Object Naming Guidelines" for additional rules to which database names should adhere
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
If you omit this clause and any of the files specified by
CONTROL_FILES already exist, Oracle returns an error.
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
DATABASE statement are added to redo log thread number 1.
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 view
If you omit the
DB_CREATE_FILE_DESTinitialization parameter (or both) is set, then Oracle creates two Oracle-managed logfiles with system-generated names, 100 MB in size, in the default logfile directory specified in the
nparameter, and if it is not set, then in the
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.
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.
This parameter is useful only if you are using Oracle in
ARCHIVELOG mode with Real Application Clusters. Specify the maximum number of archived redo log files for automatic media recovery Real Application Clusters. 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.
Specify the initial sizing of the datafiles section of the control file at
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
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 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 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
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 the AL16UTF16 character set as the database character set.
Oracle9i Globalization Support Guide for more information about choosing a character set
Specify the national character set used to store data in columns specifically defined as
UTF8). The default is '
Oracle9i Globalization Support Guide for information on Unicode datatype support.
Specify one or more files to be used as datafiles. All these files become part of the
If you omit this clause:
DB_CREATE_FILE_DESTinitialization parameter is set, Oracle creates a 100 MB Oracle-managed datafile with a system-generated name in the default file destination specified in the parameter.
DB_CREATE_FILE_DESTinitialization parameter is not set, Oracle creates one datafile whose name and size depend on your operating system.
autoextend_clause to enable or disable the automatic extension of a new datafile or tempfile. If you do not specify this clause, these files are not automatically extended.
ON to enable autoextend.
OFF to turn off autoextend if is turned on.
NEXT clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. Use
M to specify this size in kilobytes or megabytes. The default is the size of one data block.
MAXSIZE clause to specify the maximum disk space allowed for automatic extension of the datafile.
UNLIMITED clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.
Specify this clause to create a default temporary tablespace for the database. Oracle 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, the
SYSTEM tablespace is the default temporary tablespace.
On some operating systems, Oracle does not allocate space for the 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. Please refer to your operating system documentation to determine whether Oracle allocates tempfile space in this way on your system.
SYSTEMtablespace in this clause.
temp_tablespace_extent clause lets you specify how the tablespace is managed.
This clause indicates that some part of the tablespace is set aside for a bitmap. All temporary tablespaces have locally managed extents, so this clause is optional.
Specify the size of the extents of the temporary tablespace in bytes. All extents of temporary tablespaces are the same size (uniform). If you do not specify this clause, Oracle uses uniform extents of 1M.
Specify in bytes the size of the tablespace extents. Use
M to specify the size in kilobytes or megabytes.
If you do not specify
SIZE, Oracle uses the default extent size of 1M.
See Also: Oracle9i Database Concepts for a discussion of locally managed tablespaces
If you have opened the instance in Automatic Undo Management mode, you can specify the
undo_tablespace_clause to create a tablespace to be used for undo data. If you want undo space management to be handled by way of rollback segments, omit this clause.
tablespace, creates the specified datafiles as part of the undo tablespace, and assigns this tablespace as the undo tablespace of the instance. Oracle will handle management of undo data using this undo tablespace. The
DATAFILEclause of this clause has the same behavior as described in "DATAFILE Clause".
SYS_UNDOTBSand assigns this default tablespace as the undo tablespace of the instance. This undo tablespace allocates disk space from the default files used by the
DATABASEstatement, and has an initial extent of 10M. Oracle handles the system-generated datafile as described in "DATAFILE Clause". If Oracle is unable to create the undo tablespace, the entire
DATABASE clause lets you specify whether your database environment is in no-data-loss mode. In this mode, Oracle places highest priority on maintaining an absolute match between the primary and standby databases. The standby database must be mounted, and no Real Application Clusters instance can have the primary database open, even in exclusive mode.
PROTECTED to indicate that the standby instance must contain at least one standby archivelog destination to be archived by the logwriter (
LGWR) process in order for the primary database to be opened and to remain open in the event the last connection from primary to standby database is lost. In a Real Application Clusters environment, Oracle will verify that the
LGWR processes of all instances that have the primary database open archive to the same standby databases.
If a connection to the last standby database is lost, Oracle will shut down the primary instance. Therefore, you should use this setting only if absolute correspondence between the primary and standby databases is more important than availability of the database.
UNPROTECTED to indicate that the instance does not require any standby databases to be maintained by the logwriter process. This is the default.
Use this setting if the absolute correspondence between the primary and standby databases is not as important as availability of the database.
To determine whether a database is in
UNPROTECTED mode, query the
STANDBY_DATABASE column of the
V$DATABASE dynamic performance view.
TIME_ZONE clause to set the time zone of the database. You can specify the time zone in two ways:
hh:mmis -12:00 to +14:00.
TZNAMEcolumn of the
V$TIMEZONE_NAMESdynamic performance view.
Oracle normalizes all
ZONE data to the time zone of the database when the data is stored on disk. If you do not specify the
TIME_ZONE clause, Oracle uses the operating system's time zone of the server. If the operating system time zone is not a valid Oracle time zone, the database time zone defaults to UTC.
The following statement creates a database and fully specifies each argument:
CREATE DATABASE sampleCONTROLFILE 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 UTF8 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';