How to Oracle create tablespace: A Comprehensive Guide

Introduction

In this tutorial, you will learn how to Oracle create tablespace, including creating permanent and temporary tablespaces, as well as creating multiple datafiles with a single tablespace creation command. Understanding how to manage tablespaces is crucial for effective database administration.

Creating a Permanent Tablespace

Permanent tablespaces store user data and schema objects. Here’s how to create a bigfile permanent tablespace:

SQL Command

CREATE BIGFILE TABLESPACE USERDATA 
LOGGING 
DATAFILE '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/USERDATA.dbf' 
SIZE 2048MB 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

Explanation

• USERDATA: Name of the tablespace.
• LOGGING: Enables logging for the tablespace.
• DATAFILE: Specifies the location and name of the datafile.
• SIZE 2048MB: Sets the size of the datafile.
• EXTENT MANAGEMENT LOCAL: Manages extents locally.
• SEGMENT SPACE MANAGEMENT AUTO: Automatically manages segment space.

Datafile Details

• Named datafile: USERDATA
• Size: 2048MB
• Location: /mnt_nfs/oradata/oracle11g/DEVOPSROLES/USERDATA.dbf

Creating a Temporary Tablespace

Temporary tablespaces are used for sorting operations and temporary data storage.

SQL Command

CREATE TEMPORARY TABLESPACE TEMP_DATA 
TEMPFILE '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/TEMP_DATA.dbf' 
SIZE 32M 
AUTOEXTEND ON 
NEXT 32M 
MAXSIZE 1048M 
EXTENT MANAGEMENT LOCAL;

Explanation

• TEMP_DATA: Name of the temporary tablespace.
• TEMPFILE: Specifies the location and name of the tempfile.
• SIZE 32M: Initial size of the tempfile.
• AUTOEXTEND ON: Enables auto-extension of the tempfile.
• NEXT 32M: Amount of space added on each extension.
• MAXSIZE 1048M: Maximum size the tempfile can grow to.
• EXTENT MANAGEMENT LOCAL: Manages extents locally.

Note

• A temporary tablespace uses tempfiles, not datafiles.

Creating Multiple Datafiles with a Single Tablespace

You can create a tablespace with multiple datafiles in a single command.

SQL Command

CREATE TABLESPACE DATA 
DATAFILE '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/DATA_01.dbf' SIZE 4M AUTOEXTEND OFF,
          '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/DATA_02.dbf' SIZE 4M AUTOEXTEND OFF,
          '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/DATA_03.dbf' SIZE 4M AUTOEXTEND OFF 
LOGGING 
EXTENT MANAGEMENT LOCAL;

Explanation

• DATA: Name of the tablespace.
• DATAFILE: Specifies multiple datafiles with their respective sizes.
• AUTOEXTEND OFF: Disables auto-extension for these datafiles.
• LOGGING: Enables logging for the tablespace.
• EXTENT MANAGEMENT LOCAL: Manages extents locally.

Conclusion

In this tutorial, we covered how to create permanent and temporary tablespaces in Oracle, as well as how to create a tablespace with multiple datafiles. Understanding these processes is essential for effective database management and optimization. For further topics, such as “Install Oracle Database 12c on Centos 7,” keep following our page! Thank you for reading the DevopsRoles page!