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

Table 2-2 summarizes the best practices for creating and managing tablespaces.

Table 2-2. Best Practices for Creating and Managing Tablespaces

Best Practice Reasoning

Create separate tablespaces for different
applications using the same database.

If a tablespace needs to be taken offline, it affects only one
application.

For an application, separate table data from index
data in different tablespaces.

Table and index data may have different storage
requirements.

Don't use the AUTOEXTEND feature for data files.
If you do use AUTOEXTEND , specify a maximum size.

Specifying a maximum size prevents a runaway SQL
statement from filling up a storage device.

Create tablespaces as locally managed. You
shouldn't create a tablespace as dictionary
managed.

This provides better performance and manageability.

For a tablespace's data file naming convention, use a
name that contains the tablespace name followed by
a two-digit number that's unique within data files for
that tablespace.

Doing this makes it easy to identify which data files are
associated with which tablespaces.

Try to minimize the number of data files associated
with a tablespace.

You have fewer data files to manage.

In tablespace CREATE scripts, use ampersand
variables to define aspects such as storage
characteristics.

This makes scripts more reusable among various
environments.

If you ever need to verify the SQL required to re-create an existing tablespace, you can do so with the
DBMS_METADATA package. First, set the LONG variable to a large value:

SQL> set long 1000000

Next, use the DBMS_METADATA package to display the CREATE TABLESPACE data definition language (DDL) for all
tablespaces within the database:

SQL> select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

■ Tip you can also use data pump to extract the ddl of database objects. see Chapter 8 for details.

Renaming a Tablespace
Sometimes, you need to rename a tablespace. You may want to do this because a tablespace was initially erroneously
named, or you may want the tablespace name to better conform to your database naming standards. Use the ALTER
TABLESPACE statement to rename a tablespace. This example renames a tablespace from TOOLS to TOOLS_DEV :

SQL> alter tablespace tools rename to tools_dev;