oracle omf管理,Oracle OMF治理

CREATE DATABASE Statement Using Oracle Managed Files: Examples

This section contains examples of theCREATE DATABASEstatement when using the Oracle Managed Files feature.

CREATE DATABASE: Example 1This example creates a database with the following Oracle Managed Files:

ASYSTEMtablespace data file in directory/u01/app/oracle/oradatathat is autoextensible up to an unlimited size.

ASYSAUXtablespace data file in directory/u01/app/oracle/oradatathat is autoextensible up to an unlimited size. The tablespace is locally managed with automatic segment-space management.

Two online log groups with two members of 100 MB each, one each in/u02/oradataand/u03/oradata.

If automatic undo management mode is enabled (the default), then an undo tablespace data file in directory/u01/app/oracle/oradatathat is 20 MB and autoextensible up to an unlimited size. An undo tablespace namedSYS_UNDOTSis created.

If noCONTROL_FILESinitialization parameter is specified, then two control files, one each in/u02/oradataand/u03/oradata. The control file in/u02/oradatais the primary control file.

The following parameter settings relating to Oracle Managed Files, are included in the initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata'

DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata'

DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata'

The following statement is issued at the SQL prompt:

CREATE DATABASE sample;

To create the database with a locally managedSYSTEMtablespace, add theEXTENTMANAGEMENTLOCALclause:

CREATE DATABASE sample EXTENT MANAGEMENT LOCAL;

Without this clause, theSYSTEMtablespace is dictionary managed. Oracle recommends that you create a locally managedSYSTEMtablespace.

CREATE DATABASE: Example 2This example creates a database with the following Oracle Managed Files:

ASYSTEMtablespace data file in directory/u01/app/oracle/oradatathat is autoextensible up to an unlimited size.

ASYSAUXtablespace data file in directory/u01/app/oracle/oradatathat is autoextensible up to an unlimited size. The tablespace is locally managed with automatic segment-space management.

Two redo log files of 100 MB each in directory/u01/app/oracle/oradata.They are not multiplexed.

An undo tablespace data file in directory/u01/app/oracle/oradatathat is 20 MB and autoextensible up to an unlimited size. An undo tablespace namedSYS_UNDOTSis created.

A control file in/u01/app/oracle/oradata.

In this example, it is assumed that:

NoDB_CREATE_ONLINE_LOG_DEST_ninitialization parameters are specified in the initialization parameter file.

NoCONTROL_FILESinitialization parameter was specified in the initialization parameter file.

Automatic undo management mode is enabled.

The following statements are issued at the SQL prompt:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata';

CREATE DATABASE sample2 EXTENT MANAGEMENT LOCAL;

This database configuration is not recommended for a production database. The example illustrates how a very low-end database or simple test database can easily be created. To better protect this database from failures, at least one more control file should be created and the redo log should be multiplexed.

CREATE DATABASE: Example 3In this example, the file size for the Oracle Managed Files for the default temporary tablespace and undo tablespace are specified. A database with the following Oracle Managed Files is created:

A 400 MBSYSTEMtablespace data file in directory/u01/app/oracle/oradata. BecauseSIZEis specified, the file in not autoextensible.

A 200 MBSYSAUXtablespace data file in directory/u01/app/oracle/oradata.BecauseSIZEis specified, the file in not autoextensible. The tablespace is locally managed with automatic segment-space management.

Two redo log groups with two members of 100 MB each, one each in directories/u02/oradataand/u03/oradata.

For the default temporary tablespacedflt_ts, a 10 MB temp file in directory/u01/app/oracle/oradata. BecauseSIZEis specified, the file in not autoextensible.

For the undo tablespaceundo_ts, a 100 MB data file in directory/u01/app/oracle/oradata. BecauseSIZEis specified, the file is not autoextensible.

If noCONTROL_FILESinitialization parameter was specified, then two control files, one each in directories/u02/oradataand/u03/oradata. The control file in/u02/oradatais the primary control file.

The following parameter settings are included in the initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata'

DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata'

DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata'

The following statement is issued at the SQL prompt:

CREATE DATABASE sample3

EXTENT MANAGEMENT LOCAL

DATAFILE SIZE 400M

SYSAUX DATAFILE SIZE 200M

DEFAULT TEMPORARY TABLESPACE dflt_ts TEMPFILE SIZE 10M

UNDO TABLESPACE undo_ts DATAFILE SIZE 100M;

See Also:

"Creating a Locally Managed SYSTEM Tablespace"