• Applications may have different storage requirements. Separate tablespaces allow for different settings for extent sizes and segment management.
• You may have some data that is purely read only. Separate tablespaces let you put a tablespace that contains only read-only data into read-only mode.
This chapter focuses on the most common and critical tasks associated with creating and maintaining tablespaces and datafiles. The next section discusses creating tablespaces, and the chapter progresses to more advanced topics such as moving and renaming datafiles.
Creating Tablespaces
You use the CREATE TABLESPACE statement to create tablespaces. The Oracle SQL reference manual contains more than 12 pages of syntax and examples for creating tablespaces. In most scenarios, you need to use only a few of the features available, namely locally managed extent allocation, and automatic segment space management. The following code snippet demonstrates how to create a tablespace that utilizes the most common features:
create tablespace tools
datafile '/ora01/dbfile/INVREP/tools01.dbf'
size 100m
extent management local
uniform size 128k
segment space management auto;
You need to modify this script for your environment. For example, the directory path, datafile size, and uniform extent size should be changed per environment requirements.
You create tablespaces as locally managed by using the EXTENT MANAGEMENT LOCAL clause. A locally managed tablespace uses a bitmap in the datafile to efficiently determine whether an extent is in use.
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT aren’t valid for extent options in locally managed tablespaces.
■
Note
A locally managed tablespace with uniform extents must be minimally sized for at least five database blocks per extent.
As you add data to objects in tablespaces, Oracle automatically allocates more extents to an associated tablespace datafile as needed to accommodate the growth. You can instruct Oracle to allocate a uniform size for each extent via the UNIFORM SIZE [size] clause. If you don’t specify a size, then the default uniform extent size is 1MB.
73
CHAPTER 4 ■ TABLESPACES AND DATAFILES
The uniform extent size that you use varies depending on the storage requirements of your tables and indexes. I usually create several tablespaces for a given application. For example, you can create one tablespace for small objects that has a uniform extent size of 512KB, one tablespace for medium-sized objects that has a uniform extent size of 4MB, one tablespace for large objects with a uniform extent size of 16MB, and so on.
Alternatively, you can specify that Oracle determine the extent size via the AUTOALLOCATE clause.
Oracle allocates extent sizes of 64KB, 1MB, 8MB, or 64MB. Using AUTOALLOCATE is appropriate when you think objects in one tablespace will be of varying sizes.
The SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage the space within the block.
When you use this clause, there is no need to specify parameters such as PCTUSED, FREELISTS, and FREELIST GROUPS. The alternative to AUTO space management is MANUAL. When you use MANUAL, you can adjust the previously mentioned parameters depending on the needs of your application. I recommend that you use AUTO and not MANUAL. Using AUTO vastly reduces that number of parameters you’d otherwise need to configure and manage.
When a datafile fills up, you can instruct Oracle to automatically increase the size of the datafile with the AUTOEXTEND feature. I recommend that you don’t use this feature. Instead, you should monitor tablespace growth and add space as necessary. Manually adding space is preferable to having a runaway SQL process that accidentally grows a tablespace until it has consumed all space on a mount point. If you inadvertently fill up a mount point that contains a control file or the Oracle binaries, you can hang your database.
If you do use the AUTOEXTEND feature, I recommend that you always specify a corresponding MAXSIZE so that a runaway SQL process doesn’t accidentally fill up a tablespace that in turn fills up a mount point.
Here’s an example of creating an autoextending tablespace with a cap on its maximum size: create tablespace tools
datafile '/ora01/dbfile/INVREP/tools01.dbf'
size 100m
autoextend on maxsize 1000m
extent management local
uniform size 128k
segment space management auto;
When you’re using CREATE TABLESPACE scripts in different environments, it’s useful to be able to parameterize portions of the script. For example, in development, you may size the datafiles at 100MB, whereas in production the datafiles may be 1000GB. Use ampersand & variables to make CREATE
TABLESPACE scripts more portable among environments.
The next listing defines ampersand variables at the top of the script, and those variables determine the sizes of datafiles created for the tablespaces:
define tbsp_large=5G
define tbsp_med=500M
--
create tablespace reg_data
datafile '/ora01/oradata/INVREP/reg_data01.dbf'
size &&tbsp_large
extent management local
uniform size 128k
segment space management auto;
--
create tablespace reg_index
datafile '/ora01/oradata/INVREP/reg_index01.dbf'
size &&tbsp_med
extent management local
uniform size 128k
segment space management auto;
74
CHAPTER 4 ■ TABLESPACES AND DATAFILES
Using ampersand variables allows you to modify the script once and have the variables reused throughout the script. You can parameterize all aspects of the script, including datafile mount points and extent sizes.
You can also pass the values of the ampersand variables in to the CREATE TABLESPACE script from the SQL*Plus command line. This lets you avoid hard-coding a specific size in the script and instead provide the sizes at runtime. To accomplish this, first define at the top of the script the ampersand variables to accept the values being passed in:
define tbsp_large=&1
define tbsp_med=&2
--
create tablespace reg_data
datafile '/ora01/oradata/INVREP/reg_data01.dbf'
size &&tbsp_large
extent management local
uniform size 128k
segment space management auto;
--
create tablespace reg_index
datafile '/ora01/oradata/INVREP/reg_index01.dbf'
size &&tbsp_med
extent management local
uniform size 128k
segment space management auto;
Now you can pass variables in to the script from the SQL*Plus command line. The following example executes a script named cretbsp.sql and passes in two values that set the ampersand variables to 5G and 500M, respectively:
SQL> @cretbsp 5G 500M
Table 4–1 summarizes the best practices for creating and managing tablespaces.
Table 4–1.
Best Practices for Managing Tablespaces
Best Practice
Reasoning
Create separate tablespaces for different
If a tablespace needs to be taken offline, it affects
applications using the same database.
only one application.
For an application, separate table data from index
Table and index data may have different storage
data in different tablespaces.
requirements.
Don’t use the AUTOALLOCATE feature for datafiles. If
Specifying a maximum size prevents a runaway
you do use AUTOALLOCATE, specify a maximum size.
SQL statement from filling up a storage device.
Create tablespaces as locally managed. You
This provides better performance and
shouldn’t create a tablespace as dictionary managed.
manageability.
For a tablespace’s datafile naming convention,
Doing this makes it easy to identify which
use a name that contains the tablespace name
datafiles are associated with which tablespaces.
followed by a two-digit number that’s unique
within datafiles for that tablespace.
75
CHAPTER 4 ■ TABLESPACES AND DATAFILES
Best Practice
Reasoning
Try to minimize the number of datafiles
You have fewer datafiles to manage.
associated with a tablespace.
In tablespace CREATE scripts, use ampersand
This makes scripts more reusable among various
variables to define aspects such as storage
environments.
characteristics.
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 FOOBAR to USERS:
SQL> alter tablespace foobar rename to users;
When you rename a tablespace, Oracle updates the name of the tablespace in the data dictionary, control files, and data headers. Keep in mind that renaming a tablespace doesn’t rename any associated datafiles. Renaming datafiles is covered later in this chapter.
■
Note
You can’t rename the SYSTEM tablespace or the SYSAUX tablespace.
Controlling the Generation of Redo
For some types of applications, you may know beforehand that you can easily re-create the data. An example might be a data-warehouse environment where you perform direct path inserts or use SQL*Loader to load data. In these scenarios, you can turn off the generation of redo for direct path loading. You use the NOLOGGING clause to do this:
create tablespace inv_mgmt_data
datafile '/ora02/dbfile/O11R2/inv_mgmt_data01.dbf'' size 100m
extent management local
uniform size 128k
segment space management auto
nologging;
If you have an existing tablespace and want to alter its logging mode, use the ALTER TABLESPACE
statement:
SQL> alter tablespace inv_mgmt_data nologging;
You can confirm the tablespace logging mode by querying the DBA_TABLESPACES view: SQL> select tablespace_name, logging from dba_tablespaces;
76
CHAPTER 4 ■ TABLESPACES AND DATAFILES
The generation of redo logging can’t be suppressed for regular INSERT, UPDATE, and DELETE
statements. For regular Data Manipulation Language (DML) statements, the NOLOGGING clause is ignored.
The NOLOGGING clause does apply, however, to the following types of DML:
• Direct path INSERT statements
• Direct path SQL*Loader
The NOLOGGING clause also applies to the following types of DDL statements:
• CREATE TABLE ... AS SELECT
• ALTER TABLE ... MOVE
• ALTER TABLE ... ADD/MERGE/SPLIT/MOVE/MODIFY PARTITION
• CREATE INDEX
• ALTER INDEX ... REBUILD
• CREATE MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW ... MOVE
• CREATE MATERIALIZED VIEW LOG
• ALTER MATERIALIZED VIEW LOG ... MOVE
Be aware that if redo isn’t logged for a table or index, and you have a media failure before the object is backed up, then you can’t recover the data. You receive an ORA-01578 error indicating that there is logical corruption of the data.
■
Note
You can also override the tablespace level of logging at the object level. For example, even if a tablespace specified as NOLOGGING, you can create a table with the LOGGING clause.
Changing a Tablespace’s Write Mode
In environments such as data warehouses, you may need to load data into tables and then never modify the data again. To enforce that all objects in a tablespace can’t be modified, you can alter the tablespace to be read-only. To do this, use the ALTER TABLESPACE statement:
SQL> alter tablespace inv_mgmt_rep read only;
One advantage of a read-only tablespace is that you only have to back it up once. You should be able to restore the datafiles from a read-only tablespace no matter how long ago the backup was made.
If you need to modify the tablespace out of read-only mode, you do so as follows: SQL> alter tablespace inv_mgmt_rep read write;
Make sure you re-enable backups of a tablespace after you place it in read/write mode.
77
CHAPTER 4 ■ TABLESPACES AND DATAFILES
■
Note
You can’t make a tablespace that contains active rollback segments read-only. For this reason, the SYSTEM
tablespace can’t be made read-only, because it contains the SYSTEM rollback segment.
In Oracle Database 11
g
and above, you can modify individual tables to be read-only. For example: SQL> alter table my_tab read only;
While in read-only mode, you can’t issue any insert, update, or delete statements against the table.
Making individual tables read/write can be advantageous when you’re doing maintenance (such as a data migration) when you want to ensure that users don’t update the data.
This example modifies a table back to read/write mode:
SQL> alter table my_tab read write;
Dropping a Tablespace
If you have a tablespace that is unused, it’s best to drop it so it doesn’t clutter your database, consume unnecessary resources, and potentially confuse DBAs who aren’t familiar with the database. Before dropping a tablespace, it’s a good practice to first take it offline: SQL> alter tablespace inv_data offline;
You may want to wait to see if anybody screams that an application is broken because it can’t write to a table or index in the tablespace to be dropped. When you’re sure the tablespace isn’t required, drop the tablespace and delete its datafiles:
SQL> drop tablespace inv_data including contents and datafiles;
■
Tip
You can drop a tablespace whether it’s online or offline. The exception to this is the SYSTEM tablespace, which can’t be dropped. It’s always a good idea to take a tablespace offline before you drop it. By doing so, you can better determine if an application is using any objects in the tablespace. If you attempt to query a table in an offline tablespace, you receive an “ORA-00376: file can’t be read at this time” error.