What is tablespace in Oracle?

In Oracle, a tablespace is a logical storage unit that organizes and manages the physical storage of data in the database. It serves as a layer between the physical data files stored on disk and the logical structure of the database.
Function and Purpose of a Tablespace:

1. Logical Storage Allocation:
   • Tablespaces group related logical structures, such as tables and indexes, into a single unit of storage.
   • This allows for better organization, management, and isolation of data.
2. Storage Management:
   • Oracle uses tablespaces to allocate space for the database objects.
   • Each tablespace consists of one or more physical data files that store the actual data.
3. Performance Optimization:
   • Tablespaces enable administrators to distribute I/O across multiple disks by associating different data files with different physical storage devices.
   • This helps optimize database performance.
4. Data Segmentation:
   • Different types of data (e.g., user data, indexes, temporary data) can be segregated into separate tablespaces for better performance and manageability.
5. Security and Access Control:
   • Tablespaces can be used to enforce security policies. For example, certain users or applications can be granted access to specific tablespaces.
6. Data Backup and Recovery:
   • Tablespaces provide a convenient mechanism for performing partial backups and recoveries.
   • Individual tablespaces can be taken offline or restored without affecting the entire database.
7. Storage Management Options:
   • Tablespaces can be configured to automatically extend their size (autoextend) or limit growth (size constraints).
   • They can also be set to read-only to protect historical or archived data.

Types of Tablespaces:

1. System Tablespace:
   • Stores system data like the data dictionary (metadata about database objects).
   • Required for database operation.
2. User Tablespaces:
   • Stores user-defined objects like tables and indexes.
3. Temporary Tablespaces:
   • Used for sorting operations and temporary data storage during query execution.
4. Undo Tablespaces:
   • Used to store undo records, which allow for transaction rollbacks and read consistency.
5. Bigfile Tablespaces:
   • Designed to support large databases by allowing a single data file to grow to several terabytes.

Example: To create a tablespace in Oracle:

CREATE TABLESPACE my_tablespace
DATAFILE '/u01/app/oracle/oradata/mydb/my_tablespace01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

This creates a tablespace named `my_tablespace` with an initial size of 100 MB, which can grow in increments of 10 MB to a maximum of 1 GB. Tablespaces are critical for effective data management, performance tuning, and ensuring database reliability and scalability.

While Oracle physically stores 1) table and 2) index data in datafiles, it manages the relationship between tables, indexes, and datafiles using a logical concept called the tablespace. A tablespace is a grouping of one or more datafiles. When you create a table or an index, you need to tell Oracle where to store that object. You do that by providing a tablespace name. In turn, the tablespace definition includes a list of one or more datafiles, which is where Oracle actually stores the data. Move your mouse over the following diagram to see how this works:

1. Table A: Table A's data is stored in Tablespace A.
2. Table B: Table B's data is stored in Tablespace A.
3. Table C: Table C's data is stored in Tablespace B.
4. Tablespace A: Tablespaces may contain data from several objects, in this case Tables A and B.
5. Tablespace B: Tablespace B contains only data from Table C.
6. Datafile A: May contain data from Table A or Table B or both.
7. Datafile B: May contain data from Table A or Table B or both.
8. Datafile C: May contain data from Table A or Table B or both.
9. Datafile D: Will only contain data from Table C.

In Oracle, a tablespace is logically mapped to one or more "datafiles", which are the physical storage units on disk. This mapping allows Oracle to store and manage the data associated with database objects in a structured and efficient manner.
Mapping Process:

1. Tablespace Level (Logical Storage):
   • A tablespace is a logical container that organizes data in the database.
   • It serves as the logical storage structure for tables, indexes, and other database objects.
2. Datafile Level (Physical Storage):
   • A datafile is a physical file on the disk that stores the actual data of a database.
   • Each datafile belongs to a single tablespace and is associated with it.
3. Mapping Relationship:
   • One-to-One or One-to-Many:
     • A tablespace can have one or more datafiles.
     • A datafile, however, can belong to only one tablespace.
   • Oracle allocates data from the datafiles within the tablespace as needed to store the database objects.

Key Points of Mapping

• When a tablespace is created, at least one datafile must be associated with it.
• As the tablespace grows and requires more storage, additional datafiles can be added.
• Datafiles can be set to automatically extend (AUTOEXTEND) to accommodate growth within a tablespace.

Examples

1. Creating a Tablespace with a Single Datafile:

   CREATE TABLESPACE example_tbs
   DATAFILE '/u01/app/oracle/oradata/mydb/example_tbs01.dbf'
   SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
   

   • The example_tbs tablespace is logically defined, and its data is physically stored in the example_tbs01.dbf datafile.
2. Adding Additional Datafiles to a Tablespace:

   ALTER TABLESPACE example_tbs
   ADD DATAFILE '/u01/app/oracle/oradata/mydb/example_tbs02.dbf' SIZE 200M;
   

   • The example_tbs tablespace now has two datafiles: example_tbs01.dbf and example_tbs02.dbf.

Querying Tablespace-Datafile Mapping To view the mapping of tablespaces to datafiles:

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_DATA_FILES;

This query returns:

• The name of the tablespace (TABLESPACE_NAME).
• The physical file name (FILE_NAME).
• The size of the datafile (SIZE_MB).

Summary
The tablespace-datafile relationship is the cornerstone of Oracle's physical and logical storage model.

• Tablespace: Logical container to organize and manage database storage.
• Datafile: Physical file on the disk that stores the actual data for the objects in the tablespace.

This separation of logical and physical storage provides flexibility for managing storage and scaling database systems.

You can see that the tablespace stands between the object (table, index, etc) that you have created, and the physical location where that object is stored. The benefit you get from this relationship is the flexibility to add datafiles as the objects in a tablespace grow. Tablespaces allow a many-to-many relationship between database objects and datafiles. Many tables and indexes may be stored in a datafile, and many datafiles may be used for one table or index. The advantage to this twofold: you can easily add new datafiles whenever you need more space, and you can spread your datafiles over multiple disks as a way to distribute the I/O load.
Overview of Tablespaces
A tablespace is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files. A database must have the SYSTEM and SYSAUX tablespaces. The following figure shows the tablespaces in a typical database.