11 Concepts About Oracle Tablespaces For DBA Aspirants

Work without love is slavery

--Mother Teresa

Tablespace is a logical storage unit within Oracle DBMS. The underlying object where the data is physically stored is called a datafile. A datafile is an operating system file assigned to a tablespace. A tablespace can have one or more datafiles. Database objects, like tables, assigned to a tablespace are stored in the physical datafiles of that tablespace. The size of all the datafiles in the system determines the size of the entire database.

When the database is created a tablespace called SYSTEM is also created, which mostly contains information about the structure and contents of the database, or data dictionary tables for the entire database. SYS and SYSTEM users own the objects in the SYSTEM tablespace.

1. Segments, Extents and Data blocks:

The highest level of logical data storage is tablespace. Next will be segments that reside in one tablespace only. Multiple segments reside in a tablespace and a datafile can contain many segments.
Further, each segment is constructed of one or more extents. A segment and all its extents are stored in one tablespace. A segment can include extents from more than one datafile, i.e. a segment can span datafiles. In order for a segment to span across multiple datafiles, it will be constructed from multiple extents, which will be located in separate datafiles.
At the lowest level, Oracle stores data in data blocks. A contiguous set of data blocks make an extent. A data block is a specific number of bytes of physical database space on the disk.

2. Types of Tablespaces:

Permanent: To permanently store your user and application data.
Undo: For managing undo data. A database can have only one undo tablespace.
Temporary:  For storing temporary data. The physical files for a temporary tablespace are called tempfiles.

3. Bigfile and Smallfile tablespaces:

A Bigfile tablespace has a single, but large datafile(or tempfile). Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management. To create a Bigfile tablespace specify the keyword BIGFILE e.g. CREATE BIGFILE TABLESPACE. Without the keyword a Smallfile tablespace will be created. We can have multiple datafiles for a Smallfile tablespace.

4. Creating a Tablespace:

 CREATE TABLESPACE mytbs DATAFILE '/opt/oracle/data/mytbs.dbf' SIZE 100M  

Query to create a tablespace named as "mytbs", with mandatory clauses and default options.

5. Creating a Tablespace with options:

 CREATE TABLESPACE mytbs1 DATAFILE '/opt/oracle/data/mytbs1.dbf' SIZE 100M  
 AUTOEXTEND ON  
 MAXSIZE UNLIMITED  
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE  
 SEGMENT SPACE MANAGEMENT AUTO;  

This will create a tablespace which will keep on AUTOEXTEND'ing the size, the MAXSIZE is unlimited.
The EXTENT MANAGEMENT is local so the database tracks extents through the use of bitmaps. And it is set to AUTOALLOCATE so the database automatically selects the appropriate and varying extent sizes for each segment. If AUTOALLOCATE is not specified the default is UNIFORM.
For Locally Managed Tablespaces we can specify SEGMENT SPACE MANAGEMENT as MANUAL or AUTO. For AUTO bitmaps are used to manage the free space within segments.

6. Creating a temporary tablespace:

 CREATE TEMPORARY TABLESPACE mytemp TEMPFILE '/opt/oracle/data/mytemp.dbf' SIZE 50M;  

Note: DATAFILEs are completely allocated and initialized at creation time, but might be not for TEMPFILEs.
An auto-extending undo tablespace named UNDOTBS1 is automatically created when we create the database. We can also explicitly create an undo tablespace with the CREATE UNDO TABLESPACE statement.

7. Dropping a tablespace:

 DROP TABLESPACE mytbs1 INCLUDING CONTENTS;  

The above statement will only drop the contents while the below statement will also delete the associated datafiles of the tablespace

 DROP TABLESPACE mytemp INCLUDING CONTENTS AND DATAFILES;  

8. Renaming a tablespace:

 ALTER TABLESPACE mytbs RENAME TO mytbs1;

9. Adding datafiles to tablespace:

 ALTER TABLESPACE mytbs ADD DATAFILE  
 '/opt/oracle/data/mytbs0.dbf' SIZE 2G;  

10. Dropping a datafile:

 ALTER TABLESPACE mytbs  
 DROP DATAFILE '/opt/oracle/data/mytbs0.dbf';  

11. Online and Offline tablespace:

 sql> ALTER TABLESPACE mytbs OFFLINE;  
 bash$ mv /opt/oracle/data/mytbs0.dbf /opt/oracle/data1/mytbs0.dbf  
 sql> ALTER TABLESPACE mytbs RENAME DATAFILE  
 '/opt/oracle/data/mytbs0.dbf' TO '/opt/oracle/data1/mytbs0.dbf';
 sql> ALTER TABLESPACE mytbs ONLINE;