Oracle Tablespace – an Overview of Types and Basic Manipulations

In 2022, Oracle is still the world’s most popular database management system, and it is not going to lose its position anytime soon. Learning Oracle is obligatory for database specialists because chances are sky-high that you will have to develop and administer an Oracle database at some point in your career. In this article, we will explore one of Oracle’s distinctive features – tablespaces. dbForge Studio for Oracle with a whole set of convenient and user-friendly tools will help us throughout this journey.

Contents

• What is a tablespace in Oracle?
• Oracle tablespace types
  • Oracle default tablespaces
  • Online and offline tablespaces in Oracle
  • Read-only tablespaces in Oracle
  • The difference between the read-only and online/offline tablespaces in Oracle
• Adding and editing tablespaces in an Oracle database
  • Creating a new Oracle tablespace
  • Extend a tablespace by adding new datafile to the tablespace
  • Extend a tablespace by resizing an existing datafile
  • How to remove the tablespace from the database
• Conclusion

What is a tablespace in Oracle?

Oracle stores data physically in so-called datafiles. These are files with .DBF extension. You can locate them on your disk. Oracle creates such files and keeps the database tables, views, indexes, and other schema objects in them. This way, it ensures the proper functioning of your Oracle database. 

The datafiles are united and organized into logical storage units – these units are called Oracle tablespaces. Unlike datafiles, tablespaces don’t have any physical incarnation in the file system. We can consider them an intermediary between the physical and logical Oracle components. In other words, the tablespace could be similar to a shared disk drive – they allow you to store, move, or delete datafiles within the storage. 

Important points to note about Oracle tablespaces: 

• A tablespace belongs to one specific database only. A datafile belongs to the specific tablespace only. You can’t share or move datafiles between tablespaces, and it is impossible to move/share tablespaces between databases.
• A tablespace must consist of at least one datafile. The maximum number of datafiles in one tablespace is 1022 (the actual number of datafiles that a tablespace can contain will depend on the OS).

Tablespaces aren’t all same. Depending on the data type and size they contain, we can bring the classification of Oracle tablespaces.

Oracle tablespace types

As we have already defined, the two primary factors are the type and the size of the data. According to them, we can distinguish the following types of tablespace in Oracle:

Oracle Tablespaces
Data Type			Data Size
Permanent tablespace	Temporary tablespace	Undo tablespace	Big File tablespace	Small File tablespace
Stores schema objects which remain after the session/transaction end.	Stores schema objects existing for the session / transaction duration only	Manages data used for roll-back transactions in automatic undo management mode.	Stores large data amounts.	The Oracle’s default tablespace type.
Stores objects in datafiles.	Stores objects in temp files.	Stores data permanently.	Consists of one single file (datafile or tempfile).	Can contain multiple datafiles (up to 1022).
			Min file size is 7 Mb (8K blocks) and 12 Mb (32K blocks).	One datafile can include up to 222 data blocks.
			Max file size is 32 TB (8K blocks) and 128 TB (32K blocks).	Is perfect for almost all Oracle tables and indexes.

A tablespace is the sum of all datafiles that belong to it. Smallfile tablespaces are the most common type, but some scenarios require a bigfile tablespace only.

Oracle default tablespaces

When you create a new Oracle database, it automatically creates several tablespaces under the definite name: 

• SYSTEM
• SYSAUX
• USERS
• UNDOTBS
• TEMP

All these tablespaces contain specific data:

• SYSTEM is a default tablespace containing the data dictionary. In particular, it covers the tables’ definitions, views, and stored procedures. Oracle manages the information automatically. 
• SYSAUX is an auxiliary tablespace that has become mandatory since Oracle 10g. It contains some indexes and non-sys-related tables that earlier belonged to the SYSTEM tablespace. SYSAUX takes those elements to reduce the load on the SYSTEM tablespace. 
• USERS is a permanent tablespace containing the application data. Oracle fills this space with the data created and entered by the users. 
• UNDOTBS is a tablespace containing the undo data for automatic undo management.  
• TEMP is a tablespace containing temporary data and indexes. Temporary tablespaces are necessary for work with large tables and clauses like DISTINCT, GROUP BY, and ORDER BY. 

Note: SYSTEM and SYSAUX are created and filled by Oracle, and users should not add/store any objects there.

A database may be small and only require the SYSTEM tablespace. However, it is better to have at least one more for the proper Oracle tablespace operation. You can store data separately there without mixing your schema objects with dictionary objects. Also, it makes the data administration more accurate and flexible.

Using multiple tablespaces offers even more benefits. You can control the space storage available to different database users, the data availability, run the partial backup and restore tasks, allocate the data storage for better performance, etc.

You can check what tablespaces are present in your database with the below Oracle tablespace query (example):

SELECT TABLESPACE_NAME from dba_tablespaces;

We are going to use dbForge Studio for Oracle to serve this purpose. It is a powerful integrated development environment, that helps Oracle SQL developers to increase PL/SQL coding speed, and provides versatile data editing tools for managing in-database and external data. You can also benefit from the vast code snippets library, advanced IntelliSense-style code completion, and automatic syntax check that PL/SQL Formatter functionality has to offer.

Simply paste the query into a new SQL window and click Execute. The result will look as follows:

As you can see, Oracle has “equipped” your database with the default tablespaces, and there are also several users’ tablespaces created for personal convenience.

Online and offline tablespaces in Oracle

Online tablespaces allow users to access all data available for reading and writing. In particular, the SYSTEM tablespace must be constantly online because Oracle needs the data dictionary to work. Online is the default mode for the Oracle tablespaces.

Offline tablespaces don’t allow users to access the data. Usually, database admins turn the tablespaces offline for maintenance and updating operations. Besides, Oracle turns the tablespaces offline in case of errors.

To switch the tablespace to the offline mode, use the below command:

ALTER TABLESPACE tablespace_name OFFLINE;

Note: You can’t turn the default SYSTEM Oracle tablespace offline. It is impossible to take an UNDO or a Temporary tablespace offline either.

To bring the offline tablespace back online, use the following command:

ALTER TABLESPACE tablespace_name ONLINE;

Read-only tablespaces in Oracle

Read-only tablespaces allow you to store files in read-only mode. Oracle won’t update files in such tablespaces, and users can omit backing up and restoring operations for such static database parts. It is possible to remove some objects from read-only tablespaces, but you can’t create or alter existing objects there.

All the newly created tablespaces are assigned the read/write mode by default. You can switch it to the read-only status with the ALTER TABLESPACE command:

ALTER TABLESPACE tablespace_name READ ONLY;

Note that an Oracle DBA must have the ALTER TABLESPACE or MANAGE TABLESPACE system privileges.

Also, pay attention to the following requirements for turning Oracle tablespaces read-only:

• The tablespace must be online to switch it to the read-only mode
• The tablespace must not have any undo information to be applied 
• You can’t set the tablespace to be read-only if it is involved in an online backup
• It is impossible to make an active UNDO tablespace or the SYSTEM default tablespace read-only

When you need to update the read-only tablespace, there is a way to do it. For that, alter this tablespace and make it write/read. After making the required changes, make sure to turn the tablespace back to the read-only mode.

The difference between the read-only and online/offline tablespaces in Oracle

Read-only tablespaces differ from offline and online tablespaces. A tablespace in Oracle can be offline/online and read-only simultaneously. When you change it from read-only to read/write and vice versa, it does not alter being online or offline.

However, offline datafiles become readable if you switch the tablespace to the read-only status. Also, you can make separate datafiles stored in a read-only tablespace online or offline. Use the below command:

ALTER TABLESPACE name DATAFILE {ONLINE|OFFLINE}

You can apply this command to read-only tablespaces and either allow or prohibit access to specific datafiles.

Adding and editing tablespaces in an Oracle database

It is quite a common issue in Oracle when a tablespace becomes too full to contain any more data. Thus, the need to enlarge tablespaces and databases occurs regularly. You can do it in three ways: 

• Add a new tablespace with at least one datafile to a database 
• Add new datafile to a tablespace
• Resize existing datafiles in a tablespace or let them grow dynamically

Further, we’ll review performing these tasks through the standard commands.

Creating a new Oracle tablespace

We use the dedicated Oracle CREATE TABLESPACE statement to add a tablespace. Assume that we want to create a new one, named TBSnew, 50Mb in size. 

Use the below script:

CREATE TABLESPACE TBSnew 
   DATAFILE 'TBSnew_data.dbf' 
   SIZE 50m;

In this statement, we have to specify three parameters:

• The tablespace name
• The path to the corresponding datafile (you can provide the full path as the file is physical)
• The size of the new tablespace

To check if our tablespace has been created successfully, we can use the following query:

SELECT 
   tablespace_name, 
   file_name, 
   bytes / 1024/ 1024 MB
FROM
   dba_data_files;

The example illustrates the most basic syntax used to create a new tablespace. The statement can be very complex, with plenty of options. You can find detailed information about this command in the Oracle official CREATE TABLESPACE documentation. 

Oracle automatically adds any newly-created table to the default user’s tablespace. However, you can change the destination and specify the tablespace to store any particular table as below:

CREATE TABLE table_name 
TABLESPACE tablespace_name;

Note: You need the “write” privilege on the specified tablespace to put a new Oracle table there and store it.

Extend a tablespace by adding new datafile to the tablespace

As the database data grows, we need to make sure the size of the database itself can keep up with it.

To extend the database in Oracle, we can add a new datafile to the tablespace with the ALTER TABLESPACE statement. The syntax will be as follows:

ALTER TABLESPACE name_of_tablespace
    ADD DATAFILE 'path_to_the_datafile'
    SIZE size_of_the_datafile;

Assume that we want to add a new datafile to our TBSnew tablespace created earlier:

ALTER TABLESPACE TBSnew
    ADD DATAFILE 'TBSnew_new_customer_data.dbf'
    SIZE 20m;

You can also use the AUTOEXTEND ON clause that sets Oracle to increase our datafile size when needed:

ALTER TABLESPACE name_of_tablespace
    ADD DATAFILE 'path_to_datafile'
    SIZE size_of_the_datafile
    AUTOEXTEND ON;

In our case, we want to add a new datafile to the existing tablespace and set Oracle to increase that file automatically further. Use the following script:

ALTER TABLESPACE TBSnew
    ADD DATAFILE 'TBSnew_new_customer_data.dbf'
    SIZE 20m
    AUTOEXTEND ON;

We have added a new datafile to the tablespace and defined its initial size. Further, when the data stored in that datafile exceeds that initially planned storage size, Oracle will increase its size automatically and rid us of unnecessary manual work.

Extend a tablespace by resizing an existing datafile

Another regular task for tablespace management in Oracle is changing the size of the datafiles, tablespaces, and databases. We can resize our database by resizing the datafile belonging to it. The ALTER DATABASE RESIZE DATAFILE statement allows us to perform this operation. The syntax is the following:

ALTER DATABASE
    DATAFILE 'path_to_datafile'
    RESIZE new_datafile_size;

In the previous examples, we added a datafile named TBSnew_new_customer_data.dbf, 20Mb in size. Assume we want to resize it, increasing its size to 30Mb:

ALTER DATABASE 
    DATAFILE 'TBSnew_new_customer_data.dbf' 
    RESIZE 30m;

Thus, we have changed the datafile size and, therefore the entire Oracle tablespace size.

How to remove the tablespace from the database

If the work scenario requires removing any tablespace from the database, you can do that using the DROP TABLESPACE statement. Depending on the conditions, you may need to remove an empty tablespace or one containing data.

Removing an empty tablespace in Oracle is the most straightforward case with DROP TABLESPACE:

DROP TABLESPACE TBSnew;

If the tablespace contains some data, you need to modify the command with the INCLUDING CONTENTS option:

DROP TABLESPACE TBSnew
    INCLUDING CONTENTS;

If the tablespace stores tables referenced by other constraints, use the CASCADE CONSTRAINTS option:

DROP TABLESPACE TBSnew
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

This statement allows you to remove both the online and offline tablespaces. However, it is better to switch an online tablespace to offline before deleting it. Thus, you ensure that Oracle does not use any objects in a tablespace meant for deletion in its sessions.

Note: It is impossible to delete the default SYSTEM tablespace. However, you can drop the SYSAUX tablespace if you start the database in the migrate mode. You must have the SYSDBA privilege to drop this tablespace.

Conclusion

The tablespace concept is one of Oracle’s distinctive features. This combination of physical storage (data files) with logical storage spaces (tablespaces) helps the database developers and admins organize the databases and work efficiently on their routines. Different types of tablespaces with their specific qualities ensure the required flexibility for database professionals. If you are looking for a faster and more convenient way to work with Oracle databases, dbForge Studio for Oracle is one of the best choices out there. With such functionality as the user-friendly PL/SQL Editor, it can simplify your everyday working routine and make your coding faster and more accurate. Do not hesitate to give dbForge Studio a try with a free 30-day trial version.