A tablespace in Oracle is a logical storage unit for storing data within an Oracle database. It is similar to a folder or directory on a computer’s file system for it provides a way to organize and manage data within the database. Tablespaces store data for one or more database objects, such as tables, indexes, or clusters.
Tablespaces are created and managed by the database administrator, providing a flexible and efficient way to store and manage the data within the database. Using tablespaces, the database administrator can control where and how data is stored and optimize the database’s performance and availability.
For example, a tablespace can store data for a particular application or set of users, or a specific database object. Tablespaces can also distribute data across multiple disks or storage devices, allowing faster access and improved performance.
Overall, tablespaces are an essential part of the Oracle database management system, providing a way to organize and manage data within the database for optimal performance and availability.
In this tutorial, we will take on the role of a database administrator and learn how to work with tablespaces in an Oracle server.
Oracle Create Tablespace Statement
In Oracle, we can use the CREATE TABLESPACE statement to create a new tablespace in the server. The following demonstrates the syntax for the CREATE TABLESPACE command.
DATAFILE 'datafile_name'
SIZE size
[REUSE | AUTOEXTEND ON | OFF]
[DEFAULT]
STORAGE ( [INITIAL size] [NEXT size] [MINIMUM size] [MAXSIZE size | UNLIMITED] [PCTINCREASE number] [BUFFER_POOL buffer_pool_name] )
[LOGGING | NOLOGGING]
[EXTENT MANAGEMENT {DICTIONARY | LOCAL [UNIFORM [SIZE size]]}]
[SEGMENT SPACE MANAGEMENT {MANUAL | AUTO}]
The statement syntax are explained below:
- tablespace_name: The name of the tablespace to be created.
- datafile_name: The name of the datafile that will be associated with the tablespace.
- size: The size of the data file in bytes.
- REUSE: Specifies that the data file should be overwritten if it already exists.
- AUTOEXTEND ON: Specifies that the data file should automatically be extended if it runs out of space.
- AUTOEXTEND OFF: Specifies that the data file should not be automatically extended.
- DEFAULT: Specifies that the tablespace should be the default tablespace for new users.
- INITIAL: The initial size of the tablespace in bytes.
- NEXT: The size of the next extent in bytes.
- MINIMUM: The minimum size of the tablespace in bytes.
- MAXSIZE: The maximum size of the tablespace in bytes.
- UNLIMITED: Specifies that the tablespace can grow indefinitely.
- PCTINCREASE: The percentage by which the tablespace should be increased when it runs out of space.
- BUFFER_POOL: The name of the buffer pool to be associated with the tablespace.
- LOGGING: Specifies that changes to the tablespace should be logged.
- NOLOGGING: Specifies that changes to the tablespace should not be logged.
- EXTENT MANAGEMENT DICTIONARY: Specifies that the dictionary-managed extent allocation should be used for the tablespace.
- EXTENT MANAGEMENT LOCAL UNIFORM SIZE: Specifies that locally managed uniform extent allocation should be used for the tablespace, with extents of the specified size.
- SEGMENT SPACE MANAGEMENT AUTO: Specifies that segment space management should be automatic for the tablespace.
- SEGMENT SPACE MANAGEMENT MANUAL: Specifies that segment space management should be manual for the tablespace.
Example Usage
The following example shows how to use the CREATE TABLESPACE command to initialize a new tablespace.
DATAFILE 'my_tbspace.dat'
SIZE 5M;
The example above should create a tablespace with the name my_tablespace of size 5MB.
To view the information of a tablespace created in Oracle, you can use the SELECT statement to query the DBA_TABLESPACES view. This view contains information about all tablespaces in the database.
Here is an example of how you can use the SELECT statement to view the information of a tablespace:
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'MY_TABLESPACE';
This statement will return all columns from the DBA_TABLESPACES view for the tablespace with the name my_tablespace. You can modify the SELECT statement to include only the columns you are interested in or use a different condition in the WHERE clause to filter the results.
Oracle Tablespaces During Creation
When you create a table in Oracle, the oracle will automatically place the table in the default tablespace defined during user creation. However, if you wish to place the table in another tablespace other than the default, we can use the tablespace as shown:
Example:
id NUMBER,
name VARCHAR2(50),
date_created DATE
)
TABLESPACE my_tablespace;
This statement creates a table called sample_table with three columns: id, name, and date_created. The table is created in the my_tablespace tablespace.
It is good to note that the size of the data inserted into a table is governed by the size of the tablespace on which that table resides.
Conclusion
You can use the CREATE TABLESPACE statement to create tablespaces in Oracle. In addition, the TABLESPACE clause in the CREATE TABLE statement is used to specify the tablespace in which created table is stored. These features can help you manage the space and organization of your database.
