Managing Storage   «Prev 

Oracle Tablespace Enhancements

Managing the SYSAUX Tablespace

  1. Some Oracle features use SYSAUX in its operation.
  2. SYSAUX is mandatory in any database.
  3. SYSAUX cannot be dropped, renamed or transported.
  4. Oracle recommends that you create the SYSAUX tablespace with a minimum size of 240MB.

Creating SYSAUX

  1. DBCA creates it automatically and asks you about its configuration.
  2. 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:
  1. If you are using Oracle Managed Files (OMF), the location will be on the OMF.
  2. If OMF is not configured, default locations will be system-determined.
  3. 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.