Tablespace Management Enhancements in Oracle Database 10g

Oracle 10g includes several small but neat enhancements related to tablespace management including:

• Default Permanent Tablespace
• Rename Tablespace
• SYSAUX Tablespace
• Multiple Temporary Tablespaces

Default Permanent Tablespace

Oracle9i introduced the concept of a default temporary tablespace to prevent people accidentally using the SYSTEM tablespace for temporary segments. Oracle 10g takes this further by including a default permanent tablespace to prevent users having their default tablespace set to SYSTEM. The DEFAULT TABLESPACE clause in the CREATE DATABASE statement allows the the default tablespace to be created and named. If this parameter is not set during creation, or needs to be changed subsequently, it can be set using the following command.

ALTER DATABASE DEFAULT TABLESPACE users;

The current settings for the default tablespaces can be viewed using the following query.

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM   database_properties
WHERE  property_name like '%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ --------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

Rename Tablespace

Renaming permanent and temporary tablespaces is now allowed in Oracle 10g (except for SYSTEM and SYSAUX) using the following command.

ALTER TABLESPACE ts_current_name RENAME TO ts_new_name;

The tablespace and all it's datafiles must be online and the database must have a COMPATIBLE setting of 10.0.0 or greater.

If the tablespace is read-only the datafile headers are not altered to reflect the name change and a message is written to the alert log to notify you of this fact. The impact on recovery is that the tablespace will be recovered to it's old name if the controlfile is recreated and datafiles containing the old headers are used.

If an undo tablespace is renamed in a instance which uses a pfile, rather than an spfile, a message is written to the alert log reminding you to change the value of the UNDO_TABLESPACE parameter.

SYSAUX Tablespace

The SYSAUX tablespace provides a single location for all non-essential database metadata. In the past the schema objects to support many database features were located in the SYSTEM tablespace. These have now been moved to the SYSAUX tablespace. As a result the SYSTEM tablespace is less cluttered and suffers less contention. In addition, the total number of tablespaces to support database features has been reduced.

The registered occupants of the SYSAUX tablespace are listed in the V$SYSAUX_OCCUPANTS view. The view includes a MOVE_PROCEDURE column which specifies the procedure name which can be used to move the components for that occupant to another tablespace. This is useful if the schema associated with one occupant grows to the point where it would benefit from it's own tablespace.

The components which take up the largest amount of space in the SYSAUX tablespace are typically the Automatic Workload Repository (AWR) and the Enterprise Manager (EM) repository. Other components, such as Oracle UltraSearch, Oracle Text and Oracle Streams, will have no significant impact on the total tablespace size unless they are used heavily.

Multiple Temporary Tablespaces

Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:

-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
 TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
 TABLESPACE GROUP temp_ts_group;

The tablespaces assigned to a group can be viewed using:

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP                  TEMP
TEMP_TS_GROUP                  TEMP2

2 rows selected.

Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:

-- Assign group as the temporary tablespace for a user. 
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;

-- Assign group as the default temporary tablespace. 
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;

A tablespace can be removed from a group using:

ALTER TABLESPACE temp2 TABLESPACE GROUP '';

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP                  TEMP

1 row selected.

There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.

-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';

-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;

no rows selected

Tablespace groups share the same namespace as tablespaces so a group and tablespace cannot share the same name.