TRAINING (ORA-6) - Delivered Tablespaces in the preconfigured Database

Delivered Tablespaces in the preconfigured Database

SYSTEM
- The SYSTEM tablespace is always created at database creation. Oracle uses it to manage the database.
- It contains the data dictionary, which is the central set of tables and views used as a read-only reference describing a particular 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 only be accessed by user SYS, or other administrative users with the required privilege.
- For system users, the default permanent tablespace remains SYSTEM.

SYSAUX
- This is an auxiliary (supplementary or supportive) tablespace to the SYSTEM tablespace. This reduces the load on the SYSTEM tablespace.
- Introduced in Oracle 10g. Components that use this as their default tablespace during installation
- Every Oracle Database 10g or higher level database must have a SYSAUX tablespace.
- SYSAUX tablesaces include Automatic Workload Repository, Oracle Streams, Oracle Text, and Enterprise Manager Repository components and products.
- Some of the components and products mentioned above earlier used to be in SYSTEM tablespace but now use the SYSAUX tablespace.

TEMP
- This tablespace stores temporary data generated when processing SQL statements. It would, for example, be used for sort work space.
- Every database should have a temporary tablespace that is assigned to users as their temporary tablespace.
- 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, then Oracle assigns this tablespace to the user.

UNDOTBS1
- This is the undo tablespace used by the database server to store undo information.
- Every database must have an undo tablespace that can be created at database creation.

USERS
- This tablespace is used to store permanent user objects and data.
- Like the TEMP tablespace, every database should have a tablespace for permanent user data that is assigned to users. Otherwise, their objects will be created in the SYSTEM tablespace, and this is not good practice.
- In the preconfigured database, USERS is assigned the default tablespace, and space for all objects created by non-system users comes from this tablespace.

EXAMPLE
- This tablespace contains the sample schemas that Oracle includes with the database. The sample schemas provide a common platform for examples. Oracle documentation and educational materials contain examples based on the sample schemas.