How to create tablespace in Oracle

In Oracle, a tablespace is a logical storage container that can hold one or more database objects, such as tables, indexes, and other schema objects.

The following is an example of a Data Definition Language (DDL) create tablespace in Oracle.

Statement to create tablespace in Oracle

CREATE TABLESPACE test_ts 
DATAFILE '/u01/app/oracle/oradata/test_ts01.dbf'
SIZE 100M 
AUTOEXTEND ON NEXT 10M 
MAXSIZE 2G
LOGGING 
ONLINE;

Let’s go through each component of this create a tablespace statement:

• CREATE TABLESPACE: This is the command to create a new tablespace.
• test_ts: This is the name of the tablespace being created.
• DATAFILE: This specifies the physical file that will store the data for the tablespace.
• ‘/u01/app/oracle/oradata/test_ts01.dbf’: This is the path and filename of the datafile.
• SIZE: This specifies the initial size of the datafile in megabytes (MB).
• 100M: This is the initial size of the datafile.
• AUTOEXTEND ON: This tells Oracle to automatically increase the size of the datafile as needed.
• NEXT 10M: This specifies the increment by which the datafile will grow when it needs to be extended.
• MAXSIZE 2G: This sets the maximum size to which the datafile can grow.
• LOGGING: This tells Oracle to log all changes made to the objects in the tablespace.
• ONLINE: This specifies that the tablespace should be available for use immediately after it is created.

By default, Oracle creates a tablespace called “SYSTEM” that is used to store system-related objects, such as the data dictionary. It is recommended that user-created objects be stored in a separate tablespace to improve manageability and performance.

Check DDL of users