Files in Support of Backup and Recovery Operations - Oracle RMAN for Absolute Beginners - page 46

If you drop a tablespace that has required objects in a production system, the results can be catastrophic. You
must perform some sort of recovery to get the tablespace and its objects back. Needless to say, be very careful when
dropping a tablespace. Table 2-3 lists recommendations to consider when you do this.

Table 2-3. Best Practices for Dropping Tablespaces

Best Practice Reasoning

Before dropping a tablespace, run a script such as this
to determine if any objects exist in the tablespace:
select owner, segment_name, segment_type
from dba_segments
where tablespace_name=upper('&&tbsp_name');

Doing this ensures that no tables or indexes exist in the
tablespace before you drop it.

Consider renaming tables in a tablespace before you
drop it.

If any applications are using tables within the tablespace
to be dropped, the application throws an error when a
required table is renamed.

If there are no objects in the tablespace, resize the
associated data files to a very small number, such as
10MB.

Reducing the size of the data files to a miniscule amount of
space quickly shows whether any applications are trying to
access objects that require space in a tablespace.

Make a backup of your database before dropping a
tablespace.

This ensures that you have a way to recover objects that
are discovered to be in use after you drop the tablespace.

Take the tablespace and data files offline before you
drop the tablespace. Use the ALTER TABLESPACE
statement to take the tablespace offline.

This helps determine if any applications or users are using
objects in the tablespace. They can't access the objects if
the tablespace and data files are offline.

When you're sure a tablespace isn't in use, use the
DROP TABLESPACE ... INCLUDING CONTENTS AND
DATAFILES statement.

This removes the tablespace and physically removes
any data files associated with it. Some DBAs don't like
this approach, but you should be fine if you've taken the
necessary precautions.

Using Oracle Managed Files
The Oracle Managed File (OMF) feature automates many aspects of tablespace management, such as file placement,
naming, and sizing. You control OMF by setting the following initialization parameters:
• DB_CREATE_FILE_DEST
• DB_CREATE_ONLINE_LOG_DEST_N
• DB_RECOVERY_FILE_DEST

If you set these parameters before you create the database, Oracle uses them for the placement of the data files,
control files, and online redo logs. You can also enable OMF after your database has been created. Oracle uses the
values of the initialization parameters for the locations of any newly added files. Oracle also determines the name of
the newly added file.
The advantage of using OMF is that creating tablespaces is simplified. For example, the CREATE TABLESPACE
statement doesn't need to specify anything other than the tablespace name. First, enable the OMF feature by setting
the DB_CREATE_FILE_DEST parameter:

SQL> alter system set db_create_file_dest='/u01';