The following tablespaces are created in the preconfigured database
- SYSTEM: The SYSTEM tablespace is used by the Oracle server to manage the database.
- It contains the data dictionary and tables that contain administrative information about the database. These are all contained in the SYS schema and can be accessed only by the SYS user or other administrative users with the required privilege.
- SYSAUX: This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that used the SYSTEM tablespace or their own tablespaces in earlier releases of Oracle Database now use the SYSAUX tablespace. Every Oracle Database 10g (or later release) database must have a SYSAUX tablespace.
- In Enterprise Manager (EM), you can see a pie chart of the contents of this tablespace. To do this, click Tablespaces on the Administration page. Select SYSAUX and click Edit. Then click the Occupants tab. After creation, you can monitor the space usage of each occupant in the SYSAUX tablespace by using EM. If you detect that a component is taking too much space in the SYSAUX tablespace, or if you anticipate that it will, you can move the occupant into a different tablespace by selecting one of the occupants and clicking Change Tablespace.
- TEMP: Your temporary tablespace is used when you execute a SQL statement that requires the creation of temporary segments (such as a large sort or the creation of an index). Just as each user is assigned a default tablespace for storing created data objects, each user is assigned a temporary tablespace. The best practice is to define a default temporary tablespace for the database, which is assigned to all newly created users unless otherwise specified. In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. This means that if no temporary tablespace is specified when the user account is created, Oracle Database assigns this tablespace to the user.
- UNDOTBS1: This is the undo tablespace used by the database server to store undo information. If a database uses Automatic Undo Management, it must have exactly one active undo tablespace per instance at any given time. This tablespace is created at database creation time.
- USERS: This tablespace is used to store permanent user objects and data. In the preconfigured database, the USERS tablespace is the default tablespace for all objects created by nonsystem users. For the SYS and SYSTEM users (the system users), the default permanent tablespace remains SYSTEM.
Recommendation
To simplify administration, it is common to have a tablespace for indexes alone.
