New Features for Administrators - Question 019

You are attempting to create an Oracle-Managed Files (OMF) tablespace in a production database with the following statement and receive the following error message:

CREATE TABLESPACE tbsl;

ORA-02199: missing DATAFILE/TEMPFILE clause

oerr ora 2199

02199, 00000, "missing DATAFILE/TEMPFILE clause"

*Cause: A CREATE TABLESPACE statement has no DATAFILE/TEMPFTLE clause.

*Action: Specify DATAFILE/TEMPFILE clause.

What is the corrective action to create the OMF based tablespace?

A. Issue the CREATE TABLESPACE tbsl DATAFILE SIZE 10M; command.

B. Issue the CREATE TABLESPACE tbsl EXTENT MANAGEMENT ORACLE; command.

C. Set the db_create_file_dest parameter using the ALTER SESSION command and re-issue the statement.

D. An OMF tablespace is not allowed; only control files and/or redo log files can be created by OMF.

Answer: C

Explanation:

You can set the DB_CREATE_FILE_DEST parameter using the ALTER SESSION command and re-issue the statement. You should define directories for OMF datafiles, redo log files and control files. Oracle9i gives you two new initialization parameters, DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n, to specify the location where Oracle will create and manage OMFs; n can take a value from 1 to 5.

Incorrect Answers

A: This command will not fix the problem.

B: There is no EXTENT MANAGEMENT ORACLE option in the CREATE TABLESPACE command.

D: An OMF tablespace is allowed; datafiles, control files and redo log files can be created by OMF.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features