Specifying CREATE DATABASE Statement Clauses

When you execute a CREATE DATABASE statement, Oracle Database performs a number of operations. The actual operations performed depend on the clauses that you specify in the CREATE DATABASE statement and the initialization parameters that you have set. Oracle Database performs at least these operations:

  • Creates the datafiles for the database

  • Creates the control files for the database

  • Creates the redo log files for the database and establishes the ARCHIVELOG mode.

  • Creates the SYSTEM tablespace

  • Creates the SYSAUX tablespace

  • Creates the data dictionary

  • Sets the character set that stores data in the database

  • Sets the database time zone

  • Mounts and opens the database for use

This section discusses several of the clauses of the CREATE DATABASE statement. It expands upon some of the clauses discussed in "Step 9: Issue the CREATE DATABASE Statement" and introduces additional ones. Many of the CREATE DATABASE clauses discussed here can be used to simplify the creation and management of your database.

The following topics are contained in this section:

Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM

The clauses of the CREATE DATABASE statement used for specifying the passwords for users SYS and SYSTEM are:

  • USER SYS IDENTIFIED BY password

  • USER SYSTEM IDENTIFIED BY password

If you omit these clauses, these users are assigned the default passwords change_on_install and manager, respectively. A record is written to the alert log indicating that the default passwords were used. To protect your database, you must change these passwords using the ALTER USER statement immediately after database creation.

Oracle strongly recommends that you specify these clauses, even though they are optional in this release of Oracle Database. The default passwords are commonly known, and if you neglect to change them later, you leave database vulnerable to attack by malicious users.

When choosing a password, keep in mind that beginning in Release 11g, passwords are case sensitive. Also, there may be password formatting requirements for your database. See the section entitled "How Oracle Database Checks the Complexity of Passwords" in Oracle Database Security Guide for more information.

Creating a Locally Managed SYSTEM Tablespace

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. The COMPATIBLE initialization parameter must be set to 10.0.0 or higher for this statement to be successful. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary-managed SYSTEM tablespace. Dictionary-managed tablespaces are deprecated.

If you create your database with a locally managed SYSTEM tablespace, and if you are not using Oracle-managed files, ensure that the following conditions are met:

  • You specify the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement.

  • You include the UNDO TABLESPACE clause in the CREATE DATABASE statement.

See Also:

About the SYSAUX Tablespace

The SYSAUX tablespace is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.

You can specify only datafile attributes for the SYSAUX tablespace, using the SYSAUX DATAFILE clause in the CREATE DATABASE statement. Mandatory attributes of the SYSAUX tablespace are set by Oracle Database and include:

  • PERMANENT

  • READ WRITE

  • EXTENT MANAGMENT LOCAL

  • SEGMENT SPACE MANAGMENT AUTO

You cannot alter these attributes with an ALTER TABLESPACE statement, and any attempt to do so will result in an error. You cannot drop or rename the SYSAUX tablespace.

The size of the SYSAUX tablespace is determined by the size of the database components that occupy SYSAUX. See Table 2-2 for a list of all SYSAUX occupants. Based on the initial sizes of these components, the SYSAUX tablespace needs to be at least 240 MB at the time of database creation. The space requirements of the SYSAUX tablespace will increase after the database is fully deployed, depending on the nature of its use and workload. For more information on how to manage the space consumption of the SYSAUX tablespace on an ongoing basis, please refer to the "Managing the SYSAUX Tablespace".

If you include a DATAFILE clause for the SYSTEM tablespace, then you must specify the SYSAUX DATAFILE clause as well, or the CREATE DATABASE statement will fail. This requirement does not exist if the Oracle-managed files feature is enabled (see "Specifying Oracle-Managed Files at Database Creation").

The SYSAUX tablespace has the same security attributes as the SYSTEM tablespace.

Note:

This documentation discusses the creation of the SYSAUX database at database creation. When upgrading from a release of Oracle Database that did not require the SYSAUX tablespace, you must create the SYSAUX tablespace as part of the upgrade process. This is discussed in Oracle Database Upgrade Guide.

Table 2-2 lists the components that use the SYSAUX tablespace as their default tablespace during installation, and the tablespace in which they were stored in earlier releases:

Table 2-2 Database Components and the SYSAUX Tablespace

Component Using SYSAUX Tablespace in Earlier Releases

Analytical Workspace Object Table

SYSTEM

Enterprise Manager Repository

OEM_REPOSITORY

LogMiner

SYSTEM

Logical Standby

SYSTEM

OLAP API History Tables

CWMLITE

Oracle Data Mining

ODM

Oracle Spatial

SYSTEM

Oracle Streams

SYSTEM

Oracle Text

DRSYS

Oracle Ultra Search

DRSYS

Oracle interMedia ORDPLUGINS Components

SYSTEM

Oracle interMedia ORDSYS Components

SYSTEM

Oracle interMedia SI_INFORMTN_SCHEMA Components

SYSTEM

Server Manageability Components

 

Statspack Repository

User-defined

Oracle Scheduler

 

Workspace Manager

SYSTEM


See Also:

"Managing the SYSAUX Tablespace" for information about managing the SYSAUX tablespace

Using Automatic Undo Management: Creating an Undo Tablespace

Automatic undo management uses an undo tablespace.To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO in your initialization parameter file. Or, omit this parameter, and the database defaults to automatic undo management. In this mode, undo data is stored in an undo tablespace and is managed by Oracle Database. If you want to define and name the undo tablespace yourself, you must include the UNDO TABLESPACE clause in the CREATE DATABASE statement at database creation time. If you omit this clause, and automatic undo management is enabled, the database creates a default undo tablespace named SYS_UNDOTBS.

See Also:

Creating a Default Permanent Tablespace

The DEFAULT TABLESPACE clause of the CREATE DATABASE statement specifies a default permanent tablespace for the database. Oracle Database assigns to this tablespace any non-SYSTEM users for whom you do not explicitly 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. Oracle strongly recommends that you create a default permanent tablespace.

See Also:

Oracle Database SQL Language Reference for the syntax of the DEFAULT TABLESPACE clause of CREATE DATABASE and ALTER DATABASE

Creating a Default Temporary Tablespace

The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement creates a default temporary tablespace for the database. Oracle Database assigns this tablespace as the temporary tablespace for users who are not explicitly assigned a temporary tablespace.

You can explicitly assign a temporary tablespace or tablespace group to a user in the CREATE USER statement. However, if you do not do so, and if no default temporary tablespace has been specified for the database, then by default these users are assigned the SYSTEM tablespace as their temporary tablespace. It is not good practice to store temporary data in the SYSTEM tablespace, and it is cumbersome to assign every user a temporary tablespace individually. Therefore, Oracle recommends that you use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.

Note:

When you specify a locally managed SYSTEM tablespace, the SYSTEM tablespace cannot be used as a temporary tablespace. In this case you must create a default temporary tablespace. This behavior is explained in "Creating a Locally Managed SYSTEM Tablespace".

See Also:

Specifying Oracle-Managed Files at Database Creation

You can minimize the number of clauses and parameters that you specify in your CREATE DATABASE statement by using the Oracle-managed files feature. You do this by specifying either a directory or Automatic Storage Management (ASM) disk group in which your files are created and managed by Oracle Database.

By including any of the initialization parameters DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n, or DB_RECOVERY_FILE_DEST in your initialization parameter file, you instruct Oracle Database to create and manage the underlying operating system files of your database. Oracle Database will automatically create and manage the operating system files for the following database structures, depending on which initialization parameters you specify and how you specify clauses in your CREATE DATABASE statement:

  • Tablespaces and their datafiles

  • Temporary tablespaces and their tempfiles

  • Control files

  • Redo log files

  • Archived redo log files

  • Flashback logs

  • Block change tracking files

  • RMAN backups

See Also:

"Specifying a Flash Recovery Area" for information about setting initialization parameters that create a flash recovery area

The following CREATE DATABASE statement shows briefly how the Oracle-managed files feature works, assuming you have specified required initialization parameters:

CREATE DATABASE mynewdb
     USER SYS IDENTIFIED BY sys_password
     USER SYSTEM IDENTIFIED BY system_password
     EXTENT MANAGEMENT LOCAL
     UNDO TABLESPACE undotbs
     DEFAULT TEMPORARY TABLESPACE tempts1
     DEFAULT TABLESPACE users;
  • The SYSTEM tablespace is created as a locally managed tablespace. Without the EXTENT MANAGEMENT LOCAL clause, the SYSTEM tablespace is created as dictionary managed, which is not recommended.

  • No DATAFILE clause is specified, so the database creates an Oracle-managed datafile for the SYSTEM tablespace.

  • No LOGFILE clauses are included, so the database creates two Oracle-managed redo log file groups.

  • No SYSAUX DATAFILE is included, so the database creates an Oracle-managed datafile for the SYSAUX tablespace.

  • No DATAFILE subclause is specified for the UNDO TABLESPACE and DEFAULT TABLESPACE clauses, so the database creates an Oracle-managed datafile for each of these tablespaces.

  • No TEMPFILE subclause is specified for the DEFAULT TEMPORARY TABLESPACE clause, so the database creates an Oracle-managed tempfile.

  • If no CONTROL_FILES initialization parameter is specified in the initialization parameter file, then the database also creates an Oracle-managed control file.

  • If you are using a server parameter file (see "Managing Initialization Parameters Using a Server Parameter File"), the database automatically sets the appropriate initialization parameters.

    See Also:

Supporting Bigfile Tablespaces During Database Creation

Oracle Database simplifies management of tablespaces and enables support for ultra-large databases by letting you create bigfile tablespaces. Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.

This section discusses the clauses of the CREATE DATABASE statement that let you include support for bigfile tablespaces.

See Also:

"Bigfile Tablespaces" for more information about bigfile tablespaces

Specifying the Default Tablespace Type

The SET DEFAULT...TABLESPACE clause of the CREATE DATABASE statement to determines the default type of tablespace for this database in subsequent CREATE TABLESPACE statements. Specify either SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, the default is a smallfile tablespace, which is the traditional type of Oracle Database tablespace. A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.

The use of bigfile tablespaces further enhances the Oracle-managed files feature, because bigfile tablespaces make datafiles completely transparent for users. SQL syntax for the ALTER TABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying datafiles.

The CREATE DATABASE statement shown in "Specifying Oracle-Managed Files at Database Creation" can be modified as follows to specify that the default type of tablespace is a bigfile tablespace:

CREATE DATABASE mynewdb
     USER SYS IDENTIFIED BY sys_password
     USER SYSTEM IDENTIFIED BY system_password
     SET DEFAULT BIGFILE TABLESPACE
     UNDO TABLESPACE undotbs
     DEFAULT TEMPORARY TABLESPACE tempts1;

To dynamically change the default tablespace type after database creation, use the SET DEFAULT TABLESPACE clause of the ALTER DATABASE statement:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

You can determine the current default tablespace type for the database by querying the DATABASE_PROPERTIES data dictionary view as follows:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES 
   WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';

Overriding the Default Tablespace Type

The SYSTEM and SYSAUX tablespaces are always created with the default tablespace type. However, you can explicitly override the default tablespace type for the UNDO and DEFAULT TEMPORARY tablespace during the CREATE DATABASE operation.

For example, you can create a bigfile UNDO tablespace in a database with the default tablespace type of smallfile as follows:

CREATE DATABASE mynewdb
...
     BIGFILE UNDO TABLESPACE undotbs
        DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
        SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

You can create a smallfile DEFAULT TEMPORARY tablespace in a database with the default tablespace type of bigfile as follows:

CREATE DATABASE mynewdb
   SET DEFAULT BIGFILE TABLSPACE
...
     SMALLFILE DEFAULT TEMPORARY TABLESPACE tempts1
        TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf' 
        SIZE 20M REUSE
...

Specifying the Database Time Zone and Time Zone File

You can specify the database time zone and the supporting time zone file.

Setting the Database Time Zone

Set the database time zone when the database is created by using the SET TIME_ZONE clause of the CREATE DATABASE statement. If you do not set the database time zone, then it defaults to the time zone of the server's operating system.

You can change the database time zone for a session by using the SET TIME_ZONE clause of the ALTER SESSION statement.

See Also:

Oracle Database Globalization Support Guide for more information about setting the database time zone

Specifying the Database Time Zone File

Two time zone files are included in the Oracle home directory. The default time zone file is $ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat. A smaller time zone file can be found in $ORACLE_HOME/oracore/zoneinfo/timezone.dat.

If you are already using the smaller time zone file and you want to continue to use it in an Oracle Database 11g environment or if you want to use the smaller time zone file instead of the default time zone file, then complete the following tasks:

  1. Shut down the database.

  2. Set the ORA_TZFILE environment variable to the full path name of the timezone.dat file.

  3. Restart the database.

If you are already using the default time zone file, then it is not practical to change to the smaller time zone file because the database may contain data with time zones that are not part of the smaller time file.

All databases that share information must use the same time zone datafile.

The time zone files contain the valid time zone names. The following information is also included for each time zone:

  • Offset from Coordinated Universal Time (UTC)

  • Transition times for Daylight Saving Time

  • Abbreviations for standard time and Daylight Saving Time

To view the time zone names in the file being used by your database, use the following query:

SELECT * FROM V$TIMEZONE_NAMES;

Specifying FORCE LOGGING Mode

Some data definition language statements (such as CREATE TABLE) allow the NOLOGGING clause, which causes some database operations not to generate redo records in the database redo log. The NOLOGGING setting can speed up operations that can be easily recovered outside of the database recovery mechanisms, but it can negatively affect media recovery and standby databases.

Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. The database never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect for objects.

See Also:

Oracle Database SQL Language Reference for information about operations that can be done in NOLOGGING mode

Using the FORCE LOGGING Clause

To put the database into FORCE LOGGING mode, use the FORCE LOGGING clause in the CREATE DATABASE statement. If you do not specify this clause, the database is not placed into FORCE LOGGING mode.

Use the ALTER DATABASE statement to place the database into FORCE LOGGING mode after database creation. This statement can take a considerable time for completion, because it waits for all unlogged direct writes to complete.

You can cancel FORCE LOGGING mode using the following SQL statement:

ALTER DATABASE NO FORCE LOGGING;

Independent of specifying FORCE LOGGING for the database, you can selectively specify FORCE LOGGING or NO FORCE LOGGING at the tablespace level. However, if FORCE LOGGING mode is in effect for the database, it takes precedence over the tablespace setting. If it is not in effect for the database, then the individual tablespace settings are enforced. Oracle recommends that either the entire database is placed into FORCE LOGGING mode, or individual tablespaces be placed into FORCE LOGGING mode, but not both.

The FORCE LOGGING mode is a persistent attribute of the database. That is, if the database is shut down and restarted, it remains in the same logging mode. However, if you re-create the control file, the database is not restarted in the FORCE LOGGING mode unless you specify the FORCE LOGGING clause in the CREATE CONTROL FILE statement.

See Also:

"Controlling the Writing of Redo Records" for information about using the FORCE LOGGING clause for tablespace creation.

Performance Considerations of FORCE LOGGING Mode

FORCE LOGGING mode results in some performance degradation. If the primary reason for specifying FORCE LOGGING is to ensure complete media recovery, and there is no standby database active, then consider the following:

  • How many media failures are likely to happen?

  • How serious is the damage if unlogged direct writes cannot be recovered?

  • Is the performance degradation caused by forced logging tolerable?

If the database is running in NOARCHIVELOG mode, then generally there is no benefit to placing the database in FORCE LOGGING mode. Media recovery is not possible in NOARCHIVELOG mode, so if you combine it with FORCE LOGGING, the result may be performance degradation with little benefit.