1Z0-082 practice question 7 of 25

In your data center, Oracle Managed Files (OMF) is used for all databases. All tablespaces are smallfile tablespaces. SALES_Q1 is a permanent…

Full set: 142 questions
In your data center, Oracle Managed Files (OMF) is used for all databases. All tablespaces are smallfile tablespaces. SALES_Q1 is a permanent user-defined tablespace in the SALES database. Examine this command which is about to be issued by a DBA logged in to the SALES database: ALTER TABLESPACE sales_q1 ADD DATAFILE; Which are two actions, either one of which you could take to ensure that the command executes successfully? (Choose two.)
Answer options
A: Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify with at least 50 Mb of available space.
B: Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space. Correct answer
C: Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space. Correct answer
D: Add the AUTOEXTEND ON clause with NEXT set to 100M.
E: Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 Mb of available space.
Correct Answer:
B. Specify a path in the DATAFILE clause of the command specifying a location with at least 100M of available space.; C. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 Mb of available space.
Explanation

The command ALTER TABLESPACE salesq1 ADD DATAFILE; relies on Oracle Managed Files (OMF) to determine the location and name of the new datafile. For this command to succeed as written, the DBCREATEFILEDEST parameter must be set to a valid directory with sufficient space (C). By default, OMF creates a 100MB autoextensible datafile, so at least 100MB must be available. Alternatively, one could modify the command to explicitly specify the datafile path and name (B), which overrides the need for DBCREATEFILEDEST and will succeed if the specified location has enough space.

Why Incorrect

A. Ensure that DBRECOVERYFILEDEST and DBCREATEFILEDEST each specify with at least 50 Mb of available space: Incorrect. DBRECOVERYFILEDEST is for the FRA, not standard datafiles. Also, the default OMF datafile size is 100MB, so 50MB is insufficient.
D. Add the AUTOEXTEND ON clause with NEXT set to 100M: Incorrect. AUTOEXTEND concerns future growth of the file after it has been created. It does not help with the initial file creation itself.
E. Ensure that DBRECOVERYFILEDEST and DBCREATEFILEDEST each specify locations with at least 50 Mb of available space: Incorrect. This is a duplicate of the flawed logic in option A, concerning the wrong parameter and an insufficient amount of space.

References

1. Oracle Database 19c

Administrator's Guide

Chapter 13

"Using Oracle Managed Files"

Section "Creating Oracle Managed Files". It states: "When you issue an ALTER TABLESPACE ... ADD DATAFILE statement without a file name

a 100 MB OMF datafile is created in the default file system directory..."

2. Oracle Database 19c

Administrator's Guide

Chapter 2

"Creating and Configuring an Oracle Database"

Section "Specifying Initialization Parameters". It specifies that DBCREATEFILEDEST defines the location for OMF datafiles and tempfiles.

3. Oracle Database 19c

SQL Language Reference

"ALTER TABLESPACE". The syntax shows that the datafiletempfilespec clause is optional if OMF is enabled

but can be used to provide an explicit file specification.

Scroll to Top