Databases Reference
In-Depth Information
Table 4-1.
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:
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 13 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;
When you rename a tablespace, Oracle updates the name of the tablespace in the data dictionary, control files,
and data file headers. Keep in mind that renaming a tablespace doesn't rename any associated data files. See the
section “Renaming or Relocating a Data File,” later in this chapter, for information on renaming data files.
