How to Create Tablespace in Oracle

In this article, we will start to learn how to create tablespace in oracle with examples. Please read our previous article where we discussed How Table Data is Stored in Oracle. We will try to understand more information about tablespace before creating a tablespace. Creating and managing is one of the major daily tasks in the DBA. A DBA needs to add the data files and check the data files.

How to Create Tablespace in Oracle

Creating tablespace is an allocation of space in the database that can contain schema objects. We need space to create objects like tables, and views. We can do this by creating a tablespace statement. We can also use EM express. Until now we didn’t try this EM express.

There are three types of tablespaces.

Permanent tablespace: This tablespace contains persistent schema objects. Objects in the permanent tablespace are stored in data files. We can mention it as a system, sysaux, and users tablespaces.
Undo Tablespace: This is another type of permanent tablespace used by oracle databases to manage to undo data.
Temporary tablespace: This tablespace contains schema objects only for the duration of a session. When we are creating an index, the temporary tablespace is used.

Note: The database must be opened and must have created tablespace privileges. Generally, only the DBA is the user who creates and manages the tablespaces.

To create sysaux tablespace we must have sysdba privilege. As of now, this sysaux tablespace creation is not required because the sysaux tablespace is already created while creating the database. Usually, we create users tablespace.

Creating Tablespace in Oracle:

To create a tablespace, we must have syntax. The syntax contains many clauses. These clauses are very important.

FILENAME and SIZE
AVAILABILITY
BLOCK SIZE
Extent Management
Logging clause
Segment Management Clause
Data Segment Compression.

FILE NAME and SIZE:

We must specify a datafile or tempfile clause when we create a tablespace. This will specify the name and location of the data file or the tempfile. We have to mention whether it is a data file or tempfile. A tablespace must have one data file or tempfile. We cannot create a tablespace without at least one file. The file contains the actual data. We must also specify the initial file size.

You can include the AUTOEXTEND ON clause to automatically extend the rile when it is full. In this case, we need to specify increment amount + max size.

We have options called the big file or small file to override the default tablespace type. The default tablespace type is temp or permanent or undo tablespace. The big file tablespace contains only one data file or temp file, which can contain up to approximately 4 billion blocks.

A small tablespace is a traditional oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million blocks.

AVAILABILITY:

We can also include the online or offline clause to make the tablespace available or not available. The default clause is online. Online or offline clauses cannot be used with the temporary tablespace. DBA_tablespaces indicates whether each tablespace is online or offline. If we query using the view dba_tablespaces we can easily find out which tablespace is online or offline. The offline tablespace cannot be used further.

BLOCK SIZE:

We can include the BLOCKSIZE to specify non-standard block sizes. In order to specify the clause, the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter must be set, and the integer you specify in this clause must correspond with the setting of one DB_nk_CACHE_SIZE parameter setting. Normally we don’t do this because the default block size is 8kb. You cannot specify non-standard block sizes for a temporary tablespace or if you intend to assign this tablespace as the temporary tablespace for any users.

Extent Management:

We can include an EXTENT MANAGEMENT clause to specify how the extent of the tablespace will be managed. We have two options AUTOALLOCATE and UNIFORM.

AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. You can specify AUTOALLOCATE for a temporary tablespace.

UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. The default size is 1 MB.

All extents of temporary tablespace are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.

If you do not specify AUTOALLOCATE or UNIFORM then the default is UNIFORM for temporary tablespaces and AUTOALLOCATE for all other types of tablespaces.

Logging Clause:

This is to specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. The logging_clause lets you specify whether the creation of a database object will be logged in the redo log file (LOGGING) or not (NOLOGGING). This is very important in backup and recovery. If we omit this clause then the default is LOGGING. This clause is not valid for a temporary or undo tablespace. This clause is only for permanent tablespaces. Oracle recommends using LOGGING. So, if we need all the objects that were created in the database need to store in a log or redo log files then we have to mention the LOGGING clause.

Segment Management Clause:

This lets you specify whether the oracle database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace.

AUTO: Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap.
MANUAL: Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists.

Data Segment Compression:

This is disabled by default. We require an expert DBA to compress the data in the database.

We can go through the below website. On the below website, we can easily understand much more about how to create tablespace and clauses.

CREATE TABLESPACE (oracle.com)

Any DBA cannot remember all this information. We can go through the manuals and search for the required manual and then go through the manual and work on the solution. We can go through google and search for any topic in the database and manual from the oracle website.

In the next article, I am going to discuss Creating a new PDB for Tablespace Practice in Oracle. Here, in this article, I try to explain How to Create Tablespace in Oracle. I hope you enjoy this How to Create Tablespace in Oracle article.