Oracle Tablespace Enhancements

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:

If you are using Oracle Managed Files (OMF), the location will be on the OMF.
If OMF is not configured, default locations will be system-determined.
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.

1) To begin exploring new tablespace types, let us start with an example involving two databases, A and B. Each one has two tablespaces, shown by the colored rectangles (T-1 through T-4) inside each database. Tablespace T-1 and T-2 are defined as transportable tablespaces. (Transporting tablespaces is an alternative way of moving or copying data from one database to another).

2) This image shows the same two database after the two transportable tablespaces have been transported. Tablespace T-1 was transported from Database A to Database B. Tablespace T-2 was cloned (copied) and then transported from Database A to Database B. Transporting a tablespace is much faster than exporting or replicating it. Transporting moves the associated data files and then builds the tablespace meta-data in the target database. It preserves indexes intact, as long as they are stored within the tablespace being transported.

3) Here is the second example. Database A contains two tablespaces, T-1 and T-2. User A has an active transaction using tablespace T-1. User B has an active transaction using tablespace T-2. You wish to modify tablespace T-1 to read only status so that you can perform a hot backup of the tablespace. Prior to Oracle8i you would have to wait for all outstanding transactions within the database to complete before this action could be completed.

With Oracle 8i, you can perform this change to READ ONLY status as soon as there are no outstanding transactions in the target tablespace. This enhancement makes it easier and faster to change a tablespace's status from updateable to read-only.

4) In this third and final example, Database A contains the same two tablespaces, T-1 and T-2. The blue rectangle marked DD represents the database's data dictionary tables. Prior to Oracle 8i, inserting data into a table required use of the data dictionary to find available space in the tablespace, as shown in the steps listed here.

5) Oracle has enhanced tablespace management to allow for locally managed tablespaces. A locally managed tablespace does not use the data dictionary to track its open and used space. Instead, the tablespace itself tracks and maintains its usage of space. This means that an insert into the tablespace requires fewer steps. In addition, there is less fragmentation and wasted space in the locally managed tablespace.