Understanding the CREATE DATABASE Statement - Oracle 10g

When you execute a CREATE DATABASE statement, Oracle Database performs (at least) 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 and the SYSTEM rollback segment
  • 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. The following topics are contained in this section:
  • Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM
  • Creating a Locally Managed SYSTEM Tablespace
  • Creating the SYSAUX Tablespace
  • Using Automatic Undo Management: Creating an Undo Tablespace
  • Creating a Default Temporary Tablespace
  • Specifying Oracle-Managed Files at Database Creation
  • Supporting Bigfile Tablespaces During Database Creation
  • Specifying the Database Time Zone and Time Zone File
  • Specifying FORCE LOGGING Mode

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 file indicating that the default passwords were used. To protect your database, you should 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.

Creating a Locally Managed SYSTEM Tablespace
When you specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement, you cause Oracle Database to create a locally managed SYSTEM tablespace, which is a tablespace for which the database determines and manages extent sizes. The COMPATIBLE initialization parameter must be set to 9.2 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.

Locally managed tablespaces provide better performance and greater ease of management than dictionary-managed tablespaces. A locally managed SYSTEM tablespace is with AUTOALLOCATE enabled by default, which means that the system determines and controls the number and size of extents. You may notice an increase in the initial size of objects created in a locally managed SYSTEM tablespace because of the autoallocate policy. It is not possible to create a locally managed SYSTEM tablespace and specify UNIFORM extent size.

When you create your database with a locally managed SYSTEM tablespace, ensure the following conditions are met:

  • A default temporary tablespace must exist, and that tablespace cannot be the SYSTEM tablespace.
    To meet this condition, you can specify the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement, or you can omit the clause and let Oracle Database create the tablespace for you using a default name and in a default location.
  • You must not create rollback segments in dictionary-managed tablespaces. Attempts to create rollback segments in a dictionary-managed tablespace will fail if the SYSTEM tablespace is locally managed.

    You can meet this condition by using automatic undo management. Oracle has deprecated the use of rollback segments. You can include the UNDO TABLESPACE clause in the CREATE DATABASE statement to create a specific undo tablespace. If you omit that clause, Oracle Database creates a locally managed undo tablespace for you using the default name and in a default location.

When the SYSTEM tablespace is locally managed, the following additional restrictions apply to other tablespaces in the database:

  • You cannot create any dictionary-managed tablespaces in the database.
  • You cannot migrate a locally managed tablespace to a dictionary-managed tablespace.
  • Preexisting dictionary-managed tablespaces can remain in the database, but only in read-only mode. They cannot be altered to read/write.
  • You can transport dictionary-managed tablespaces into the database, but you cannot set them to read/write.

Oracle Database lets you migrate an existing dictionary-managed SYSTEM tablespace to a locally managed tablespace using the DBMS_SPACE_ADMIN package. However, there is no procedure for backward migration.

Creating 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 and that you, as a DBA, must maintain. Other functionality or features that previously used the SYSTEM tablespace can now use the SYSAUX tablespace, thus reducing 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.

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.

If you issue the CREATE DATABASE statement with no other clauses, then the software creates a default database with datafiles for the SYSTEM and SYSAUX tablespaces stored in system-determined default locations, or where specified by an Oracle-managed files initialization parameter.

The SYSAUX tablespace has the same security attributes as the SYSTEM tablespace. We 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:

Database Components and the SYSAUX Tablespace

Database Components and the SYSAUX TablespaceDatabase Components and the SYSAUX Tablespace

The installation procedures for these components provide the means of establishing their occupancy of the SYSAUX tablespace.

Using Automatic Undo Management: Creating an Undo Tablespace
The use of rollback segments for storing rollback information has been deprecated. Instead, you should use automatic undo management, which in turn uses an undo tablespace. Doing so requires the use of a different set of initialization parameters, and optionally, the inclusion of the UNDO TABLESPACE clause in your CREATEDATABASE statement.

To enable automatic undo management mode, set the UNDO_MANAGEMENT initialization parameter to AUTO in your initialization parameter file. In this mode, rollback information, referred to as undo data, is stored in an undo tablespace rather than rollback segments and is managed by Oracle Database. If you want to define and name the undo tablespace yourself, you must also include the UNDO TABLESPACE clause at database creation time. If you omit this clause, and automatic undo management is enabled, then the database creates a default undo tablespace named SYS_UNDOTBS.

Creating a Default Permanent Tablespace
The DEFAULT TABLESPACE clause of the CREATE DATABASE statement specifies a default permanent tablespace for the database. Oracle Database assign 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.

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 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.

You can add or the default temporary tablespace after database creation. You do this by creating a new temporary tablespace or tablespace group with a CREATE TEMPORARY TABLESPACE statement, and then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new default temporary tablespace.

The following statement assigns a new default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;

The new default temporary tablespace must already exist. When using a locally managed SYSTEM tablespace, the new default temporary tablespace must also be locally managed.

You cannot drop or take offline a default temporary tablespace, but you can assign a new default temporary tablespace and then drop or take offline the former one. You cannot change a default temporary tablespace to a permanent tablespace.

Users can obtain the name of the current default temporary tablespace by querying the PROPERTY_NAME and PROPERTY_VALUE columns of the DATABASE_PROPERTIES view. These columns contain the values "DEFAULT_TEMP_TABLESPACE" and the default temporary tablespace name, respectively.

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 either by specifying a directory in which your files are created and managed by Oracle Database, or by using Automatic Storage Management. When you use Automatic Storage Management, you specify a disk group in which the database creates and manages your files, including file redundancy and striping.

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
  • Temporary tablespaces
  • Control files
  • Redo log files
  • Archive log files
  • Flashback logs
  • Block change tracking files
  • RMAN backups

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


  • 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 clause, so the database creates an Oracle-managed datafile for the undo tablespace.
  • 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.

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.

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:


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:


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:

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


Specifying the Database Time Zone and Time Zone File
Oracle Database lets you specify the database default time zone and lets you choose the supporting time zone file.

Specifying the Database Time Zone
You set the database default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted, the default database time zone is the operating system time zone. The database time zone can be changed for a session with an ALTER SESSION statement.

Specifying the Database Time Zone File
This section provides information on the time zone files used to support the database time zone, specifically on Solaris platforms. Names of directories, filenames, and environment variables may differ for on other platforms but will probably be the same for all UNIX platforms. Two time zone files are provided in the Oracle Database installation directory:

  • $ORACLE_HOME/oracore/zoneinfo/timezone.dat
    This is the default file. It contains the most commonly used time zones and is smaller, thus enabling better database performance.
  • $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
    This file contains a larger set of defined time zones and should be used if you require zones that are not defined in the default timezone.dat file. Use of this larger set of time zone information can affect performance.

To enable the use of the larger time zone datafile, do the following:

  1. Shut down the database.
  2. Set the environment variable ORA_TZFILE to the full path name of the location for the timezlrg.dat file.
  3. Restart the database.

Once the larger timezlrg.dat is used, it must continue to be used unless you are sure that none of the nondefault zones are used for data that is stored in the database. Also, 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 included for each zone (note that abbreviations are only used in conjunction with the zone names):

  • Offset from UTC (formerly GMT)
  • Transition times for daylight savings
  • Abbreviation for standard time
  • Abbreviation for daylight savings time

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

SELECT * FROM V$TIMEZONE_NAMES;

The time zone names contained in both the larger and smaller time zone files are listed in the Oracle Database Globalization Support Guide.

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.

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.

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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics