Understanding the CREATE DATABASE Statement

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

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.

A locally managed SYSTEM tablespace has 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 that 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 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.

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

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 Filesat Database Creation").

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.

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:

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

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. 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 also 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 (by setting the UNDO_MANAGEMENT initialization parameter to AUTO), 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 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.

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.

You can add or change 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:

CREATE DATABASE rbdb1
     USER SYS IDENTIFIED BY pz6r58
     USER SYSTEM IDENTIFIED BY y1tz5p
     UNDO TABLESPACE undotbs
     DEFAULT TEMPORARY TABLESPACE tempts1;

• 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 (see "Managing Initialization Parameters Using a Server Parameter File"), the database automatically sets the appropriate initialization parameters.

  See Also:

  • Chapter 15, "Using Oracle-Managed Files", for information about the Oracle-managed files feature and how to use it

  • Oracle Database Storage Administrator's Guide. for information about Automatic Storage Management

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.

CREATE DATABASE rbdb1
     USER SYS IDENTIFIED BY pz6r58
     USER SYSTEM IDENTIFIED BY y1tz5p
     SET DEFAULT BIGFILE TABLESPACE
     UNDO TABLESPACE undotbs
     DEFAULT TEMPORARY TABLESPACE tempts1;

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES 
   WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';

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

CREATE DATABASE rbdb1
   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.

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.

ALTER DATABASE NO FORCE LOGGING;