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