Overview of Table spaces
A table space is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a table space stores data in one or more data files or temp files. A database must have the SYSTEM and SYS AUX table spaces.
Permanent Table spaces
A permanent table space groups persistent schema objects. The segments for objects in the table space are stored physically in data files. Each database user is assigned a default permanent table space. A very small database may need only the default SYSTEM and SYSAUX table spaces. However, Oracle recommends that you create at least one table space to store user and application data. You can use table spaces to achieve the following goals:
■Control disk space allocation for database data
■Assign a quota (space allowance or limit) to a database user
■Take individual table spaces online or offline without affecting the availability of the whole database
■Perform backup and recovery of individual table spaces
■Import or export application data by using the Oracle Data Pump utility
■Create a transportable table space that you can copy or move from one database to another, even across platforms
The SYSTEM Table space
The SYSTEM table space is a necessary administrative table space included with the database when it is created. Oracle Database uses SYSTEM to manage the database. The SYSTEM table space includes the following information, all owned by the SYS user:
■The data dictionary
■Tables and views that contain administrative information about the database
■Compiled stored objects such as triggers, procedures, and packages
The SYSTEM table space is managed as any other table space, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM table space.By default, Oracle Database sets all newly created user table spaces to be locally managed. In a database with a locally managed SYSTEM table space, you cannot create dictionary-managed table spaces (which are deprecated). However, if you execute the CREATE DATABASE statement manually and accept the defaults, then the SYSTEM table space is dictionary managed. You can migrate an existing dictionary-managed SYSTEM table space to a locally managed format.
The SYSAUX Table space
The SYSAUX table space is an auxiliary table space to the SYSTEM table space. The SYSAUX table space provides a centralized location for database metadata that does not reside in the SYSTEM table space. It reduces the number of table spaces created by default, both in the seed database and in user-defined databases. Several database components, including Oracle Enterprise Manager and Oracle Streams, use the SYSAUX table space as their default storage location. Therefore, the SYSAUX table space is created automatically during database creation or upgrade.
During normal database operation, the database does not allow the SYSAUX table space to be dropped or renamed. If the SYSAUX table space becomes unavailable, then core database functionality remains operational. The database features that use the SYSAUX table space could fail, or function with limited capability.
Note: Oracle strongly recommends that you use Database Configuration Assistant (DBCA) to create new databases so that all table spaces, including SYSTEM, are locally managed by default.