Tablespace in database

A tablespace belongs to only one database and has at-least one datafile that is used to store data for the associated Table Space. It can have multiple datafiles. Due to this fact, very large large databases can be created. A datafile can only be associated with one Table Space. These datafiles are stored in various Storage Systems and is comprised of Data Blocks. Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

A database administrator can use tablespaces to do the following:

• control disk space allocation for database data
• assign specific space quotas for database users
• control availability of data by taking individual tablespaces online or offline
• perform partial database backup or recovery operations
• allocate data storage across devices to improve performance.

There are three types of Table Spaces:

1. Permanent Table Space – It is used to store all of the permanent data like table data indexes. This information is permanent until a users or a DBA deletes the data that is available in the database.
2. UNDO Table Space – It is used to store all of the UNDO data. This UNDO data is used to provide read consistency for the select statements and rollback a transaction if necessary.
3. Temporary Table Space – It is used to store temporary data. For example, when an SQL query is run, this Temporary Table Space is used to store data temporarily for sort and join operations.

There are also some Table Spaces which are pre-configured as part of the database installation. These are:

• SYSTEM – It is used by the Oracle Server to manage the database. It contains the data dictionary and tables that contain the administrative information about the database. Not all the users are given the access to this Table Space. This cannot be renamed or dropped or even this Table Space cannot be taken offline. It is not recommended to store the user data in a SYSTEM Table Space.
• SYSAUX – It stands for System Auxiliary. It is an auxiliary Table Space to the System Table Space. From Oracle 10G, this system auxiliary table space is mandatory. Some of the components and product which were previously stored in the system table space are now stored in this Table Space. It has same properties as the SYSTEM Table Space.
• TEMP – This is used to store all the temporary data.
• UNDOTBS1 – It is used to store UNDO data.
• USERS – This is used to store all the data created by the users. Normally during table creation the DBA assigns a Table Space where the table data needs to be stored. If the DBA doesn’t mention any Table Space, then by default all the data related to that table is stored in this Table Space.
• EXAMPLE – This Table Space contains all the sample schemas that are installed when you create the database. If the sample schemas are not installed, then this Table Space is not created.
  The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.

The size of a database can be enlarged in three ways:

• Add a datafile to a tablespace – When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace.
• Add a new tablespace – An additional tablespace can be created which will have at least one data file.
• Increase the size of a datafile – The third option is to change a datafiles’ size or let data files in existing tablespaces grow dynamically as more space is needed.