The first feature we will deal with is the new SYSAUX tablespace.
The SYSAUX tablespace provides storage of non-sys-related tables and
indexes that traditionally were placed in the SYSTEM tablespace. For
example, the tables and indexes that were previously owned by the
system user can now be specified for a SYSAUX tablespace.
Unfortunately, Oracle still places the SCOTT schema and the other
demonstration schemas in the SYSTEM tablespace. Go figure.
The SYSAUX tablespace is specified with the CREATE DATABASE command.
This is demonstrated in the example database creation script in Figure
1.1.
CREATE DATABASE test
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/usr/oracle/OraHome1/oradata/aultdb1/test/system01.dbf' SIZE
300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/usr/oracle/OraHome1/oradata/aultdb1/test/sysaux01.dbf'
SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/usr/oracle/OraHome1/oradata/aultdb1/test/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED EXTENT
MANAGEMENT LOCAL
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/usr/oracle/OraHome1/oradata/aultdb1/test/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/usr/oracle/OraHome1/oradata/aultdb1/test/redo01.log')
SIZE 10240K,
GROUP 2 ('/usr/oracle/OraHome1/oradata/aultdb1/test/redo02.log') SIZE
10240K,
GROUP 3 ('/usr/oracle/OraHome1/oradata/aultdb1/test/redo03.log') SIZE
10240K
USER SYS IDENTIFIED BY "password" USER SYSTEM IDENTIFIED BY
"password";
Oracle10g sysaux
With the new SYSAUX tablespace, Oracle comes closer to providing all
the needed tablespaces for a truly OFA-compliant database right out of
the box. With just one CREATE DATABASE command we can specify the
SYSTEM tablespace, the TEMPORARY tablespace, the AUXSYS
tablespace,
the default UNDO tablespace, and the redo logs.
Of course, with the Oracle Managed Files option you can create an
entire database with a single command, but the database created is not
suitable for production use and is not OFA-compliant.
The SYSAUX tablespace is required in all new 10g databases.
Only the SYSAUX tablespace datafile location is specified. Oracle
specifies the remainder of the tablespace properties including:
online
permanent
read write
extent managment local
segment space management auto
If a datafile is specified for the SYSTEM tablespace, then one must be
specified for the SYSAUX tablespace as well. If one is not specified,
then the CREATE DATABASE command will fail. The only exception is for
an Oracle Managed File system.
During any update of a database to Oracle Database 10g, a
SYSAUX tablespace must be created or the upgrade will fail. The SYSAUX
tablespace has the same security profile as the SYSTEM tablespace.
However, loss of the SYSAUX tablespace will not result in a database
crash, only the functional loss of the schemas it contains.
