ORACLE- Tablespaces and datafiles

Tablespaces:

It is a unit of storage where ur database is kept. There may be one or more tablespaces depending on the size of ur database.

Datafiles:

Each tablespace comprises of one or more datafiles.To increase tablespace, you need to add datafiles to the tablespace.

Types of tablespaces:

---- SYSTEM tablespace contains the data dictionary.

----USERS tablespace is used to store user data(table, indexes etc)

----The SYSAUX tablespace provides a single location for all non-essential database metadata. In the past the schema objects to support many database features were located in the SYSTEM tablespace. These have now been moved to the SYSAUX tablespace. As a result the SYSTEM tablespace is less cluttered and suffers less contention.

----TEMPORARY tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory space will be allocated in a temporary tablespace for doing the sort operation

----UNDO tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces.