Oracle Tablespace Enhancements

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Managing the SYSAUX Tablespace

� Some Oracle features use SYSAUX in its operation.

� SYSAUX is mandatory in any database.

� SYSAUX cannot be dropped, renamed or transported.

� Oracle recommends that you create the SYSAUX tablespace with a minimum size of 240MB.

Creating SYSAUX

� DBCA creates it automatically and asks you about its configuration.

� Can be included in the manual database creation:

CREATE DATABASE mydb
USER SYS IDENTIFIED BY mysys
USER SYSTEM IDENTIFIED BY mysystem
..
SYSAUX DATAFILE 'c:\..\sysaux01.dbf' SIZE 500M

If you omit the SYSAUX clause, Oracle will create the SYSAUX tablespace automatically with their datafiles in location defined by the following rules:

o If you are using Oracle Managed Files (OMF), the location will be on the OMF.

o If OMF is not configured, default locations will be system-determined.

o If you include the DATAFILE clause for the SYSTEM tablespace, you must use the DATAFILE clause for the SYSAUX tablespace as well, unless you are using OMF. You can use ALTER TABLESPACE command to add a datafile though.

Relocating SYSAUX Occupants

If there is a severe space pressure on the SYSAUX tablespace, you may decide to move components out of the SYSAUX tablespace to a different tablespace.

� Query the column SPACE_USAGE_KBYTES in the V$SYSAUX_OCCUPANTS to how much of the SYSAUX tablespace�s space each of its occupants is currently using.

� Query the column MOVE_PROCEDURE to obtain the specific procedure you must use in order to move a given occupant out of the SYSAUX tablespace.

SQL> exec dbms_wm.move_proc('DRSYS');

Note: You can�t relocate the following occcupants of the SYSAUX tablespace: STREAMS, STATSPACK, JOB_SCHEDULER, ORDIM, ORDIM/PLUGINS, ORDIM/SQLMM, and SMC.

Renaming Tablespaces

In Oracle Database 10g, you can rename tablespaces:

ALTER TABLESPACE users RENAME TO users_new

Restrictions:

� Your compatibility level must be set to 10.0 or higher.

� You can�t rename the SYSTEM or SYSAUX tablespace, or offline tablespaces.

� If the tablespace is read-only, the datafile headers aren�t updated, although the control file and the data dictionary are.

Renaming Undo Tablespace

� If database started using init.ora file, Oracle retrieves a message that you should set value of UNDO_TABLESPACE parameter.

� If database started using spfile, Oracle will automatically write the new name for the undo tablespace in your spfile.

Specifying the Default Permanent Tablespace During Database Creation

Use DEFAULT TABLESPACE clause in the CREATE
DATABASE command
CREATE DATABASE mydb
...
DEFAULT TABLESPACE deftbs DATAFILE ...

If DEFAULT TABLESPACE not specified, SYSTEM tablespace will be used.

Note: The users SYS, SYSTEM, and OUTLN continue to use the SYSTEM tablespace as their default permanent tablespace.

After Database Creation Using SQL

Use ALTER DATABASE command as follows:

ALTER DATABASE DEFAULT TABLESPACE new_tbsp;

Using the Database Control

1. Database Control home page, Administration, Storage Section, Tablespaces.

2. Edit Tablespace page, select the Set As Default Permanent Tablespace option in the Type section. Then click Apply.

Viewing Default Tablespace Information

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE
PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE'

Temporary Tablespace Groups

A temporary tablespace group is a list of temporary tablespaces.

It has the following advantages:

� You define more than one default temporary tablespace, and a single SQL operation can use more than one temporary tablespace for sorting. This prevents large tablespace operations from running out of temporary space.

� Enables one particular user to use multiple temporary tablespaces in different sessions at the same time

� Enables the slave processes in a single parallel operation to use multiple tablespaces

Creating a Temporary Tablespace Group

You implicitly create a temporary tablespace group when you specify the TABLESPACE GROUP clause in a CREATE TABLESPACE statement:

CREATE TEMPORARY TABLESPACE temp_old TEMPFILE
'/u01/oracle/oradata/temp01.dbf' SIZE 500M
TABLESPACE GROUP group1;

You can also create a temporary tablespace group by:

ALTER TABLESPACE temp_old
TABLESPACE GROUP group1

Note: If you specify the NULL or '' tablespace group, it is equivalent to the normal temporary tablespace creation statement (without any groups).

Setting a Group As the Default Temporary Tablespace for the Database

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE
group1

Assigning a Temporary Tablespace Group to Users

CREATE USER sam IDENTIFIED BY sam
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE group1;
ALTER USER SAM TEMPORARY TABLESPACE GROUP2;

Viewing Temporary Tablespace Group Information

Use the following views: