Tablespaces

A database consists of one or more tablespaces. A tablespace is a logical grouping of one or more physical datafiles or tempfiles, and is the primary structure by which the database manages storage.

There are various types of tablespaces, including the following:

Permanent tablespaces

These tablespaces are used to store system and user data. Permanent tablespaces consist of one or more datafiles. In Oracle Database XE, all your application data is by default stored in the tablespace named USERS. This tablespace consists of a single datafile that automatically grows (autoextends) as your applications store more data.
Temporary tablespaces

Temporary tablespaces improve the concurrency of multiple sort operations, and reduce their overhead. Temporary tablespaces are the most efficient tablespaces for disk sorts. Temporary tablespaces consist of one or more tempfiles. Oracle Database XE automatically manages storage for temporary tablespaces.
Undo tablespace

Oracle Database XE transparently creates and automatically manages undo data in this tablespace.

When a transaction modifies the database, Oracle Database XE makes a copy of the original data before modifying it. The original copy of the modified data is called undo data. This information is necessary for the following reasons:
- To undo any uncommitted changes made to the database in the event that a rollback operation is necessary. A rollback operation can be the result of a user specifically issuing a ROLLBACK statement to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.
- To provide read consistency, which means that each user can get a consistent view of data, even while other uncommitted changes may be occurring against the data. For example, if a user issues a query at 10:00 a.m. and the query runs for 15 minutes, then the query results should reflect the entire state of the data at 10:00 a.m., regardless of updates or inserts by other users during the query.
  
  See Oracle Database Concepts for a discussion of read consistency.
- To support the Flashback Query feature, which enables you to view or recover older versions of data. See "Viewing and Restoring Historical Data with Flashback Query" for more information.

Table: Tablespaces and Descriptions describes the tablespaces included in Oracle Database XE.

Tablespaces and Descriptions

Tablespace	Description
SYST EM	This tablespace is automatically created when Oracle Database XE is installed. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for the database. It also contains various tables and views that contain administrative information about the database. These are all contained in the `SYS` schema, and can be accessed only by user `SYS` or other administrative users with the required privilege.
SYS AUX	This is an auxiliary tablespace to the `SYSTEM` tablespace, and is also automatically created upon installation. Some database components and products use this tablespace. The `HR` sample schema is also stored in the `SYSAUX` tablespace.
TEMP	This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace is used for sort work space. The `TEMP` tablespace is specified as the default temporary tablespace for every user.
UND O	This is the tablespace used by the database to store undo information.
US ERS	This tablespace is used to store permanent user objects and data. In Oracle Database XE, `USERS` is the assigned default tablespace for all users except the `SYS` user, which has the default permanent tablespace of `SYSTEM`.

Note:

You can create additional permanent tablespaces in Oracle Database XE, although typically there is no need to do so. One situation where you may have to create new permanent tablespaces is if you are importing objects from another Oracle database and the import file specifies tablespace names. See the CREATE TABLESPACE command in Oracle Database SQL Reference, and "Exporting and Importing Data" for more information.