New Features for Administrators - Question 057

You want to drop the TBS1 tablespace from your database. You also want to delete the corresponding data files automatically, and not have to do it manually. What should you do?

A. Use the DROP DATAFILE command

B. Use the DROP TABLESPACE command

C. Ensure that all database files are Oracle Managed Files before using the DROP TABLESPACE command

D. Ensure that the DB_FILE_CREATE_DEST initialization parameter is set before using the DROP TABLESPACE command

Answer: B

Explanation:

You can drop the tablespace and the segments, and also delete the OS datafiles with the following command: DROP TABLESPACE tablespace_name INCLUDING CONTENTS and DATAFILES.

Incorrect Answers

A: You cannot achieve this purpose with the DROP DATAFILE command.

C: You can delete not-OMF datafiles also using the DROP TABLESPACE command.

D: To delete not-OMF datafiles you don’t need to be ensure that the DB_FILE_CREATE_DEST initialization parameter is set before using the DROP TABLESPACE command.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 157-158

Chapter 3: Manageability Enhancements

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

Chapter 1: Oracle9i Database Administration and Management Features