How to create tablespace in Oracle

oracle create tablespace

We will be discussing Oracle create tablespace statement/oracle create tablespace 11g/12c/19c in detail in this post. I will through light on all the option on create tablespace statement.I would also be discussing ,how to alter the tablespace to read-only, offline the tablespace,how to move tablespace to another location,drop the tablespace. Apart from that, how you can OMF for managing the tablespace. I hope you will like this post. I will be looking forward for feedback on this post

Table of Contents

What is Oracle Tablespace?

Tablespace is a logical storage unit in Oracle Database. Oracle Tablespace consists of one or more data files.

Tablespace is further divided into logical unit Segments, Segment is divided into Extent and Extent into Block

Various types of Tablespace are BIGFILE, SYSTEM, SYSAUX, and UNDO

Basics About Segment/Extent/Data Block

It is a space allocated for a specific logical structure (table/index, partition, etc.), cannot span tablespaces but it can span data files belonging to the tablespace. It is made up of one or more extents

It is a set of contiguous data blocks, that cannot span a datafile (must exist in only one). When the segment grows more extents are allocated.

The smallest unit of data in the Oracle server, one or more blocks corresponds to one or more operating system blocks (should be a multiple of operating system block size), initial size determined by DB_BLOCK_SIZE parameter in the parameter file

We will check Oracle Create tablespace statement later in this post

oracle database logical structure

What are Datafiles in Oracle?

-It is a physical structure to store oracle data
-One or more physical datafiles are logically grouped to make a tablespace
-A Datafile can be associated with only one tablespace

oracle tablespace and datafiles

Oracle Create Tablespace statement

The tablespace can be created by the user having sysdba privilege to hold various tables and index objects.

Complete Syntax for Create tablespace statements.

CREATE [TEMPORARY / UNDO] TABLESPACE BIGFILE | SMALLFILE DATAFILE / TEMPFILE '' SIZE BLOCKSIZE AUTOEXTEND < [OFF/ON (NEXT MAXSIZE) / UNLIMITED] > LOGGING/NOLOGGING (LOGGING default)FORCE LOGGING ENCRYPTION ENCRYPT | DECRYPT ONLINE/OFFLINE (Online default) EXTENT MANAGEMENT < [DICTIONARY] / [LOCAL Default (AUTOALLOCATE / UNIFORM SIZE )] > AUTO SEGMENT MANAGEMENT < AUTO/MANUAL>PERMANENT / TEMPORARY (Permanent default) MINIMUM EXTENT DEFAULT STORAGE < [INITIAL ] [NEXT ] [PCTINCREASE ] [MINEXTENTS ] [MAXEXTENTS / UNLIMITED] [FREELISTS ] [FREELIST GROUPS ] [OPTIMAL /NULL] [BUFFER_POOL < DEFAULT/KEEP/RECYCLE >] >

For Container Database starting from 12c, please login to the corresponding PDB to create tablespace

alter session set container='TESTPDB'; CREATE [TEMPORARY / UNDO] TABLESPACE BIGFILE | SMALLFILE DATAFILE / TEMPFILE '' SIZE BLOCKSIZE AUTOEXTEND < [OFF/ON (NEXT MAXSIZE) / UNLIMITED] > LOGGING/NOLOGGING (LOGGING default)FORCE LOGGING ONLINE/OFFLINE (Online default) EXTENT MANAGEMENT < [DICTIONARY] / [LOCAL Default (AUTOALLOCATE / UNIFORM SIZE )] > AUTO SEGMENT MANAGEMENT < AUTO/MANUAL>PERMANENT / TEMPORARY (Permanent default) MINIMUM EXTENT DEFAULT STORAGE < [INITIAL ] [NEXT ] [PCTINCREASE ] [MINEXTENTS ] [MAXEXTENTS / UNLIMITED] [FREELISTS ] [FREELIST GROUPS ] [OPTIMAL /NULL] [BUFFER_POOL < DEFAULT/KEEP/RECYCLE >] >

Various Options of Create tablespace statements are explained below

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (232) blocks. The minimum size of the single data file or temp file is 12 megabytes (MB) for a tablespace with 32K blocks and 7MB for a tablespace with 8K blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

See also ORA-01017 Oracle Error Resolution Tips

A smallfile 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 (222) blocks.

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace that is set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restriction on Bigfile Tablespaces

You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE clause.

Temp or temporary tablespaces are used to store data with a short lifespan (transient data)

Undo tablespaces are used to store “before image” data that can be used to undo transactions

If nothing is given then it is a default tablespace that can store normal table/index segments

Specifies the location and name of the datafile

specifies the size of datafile in Kb/Mb

specified the block size for the tablespace

specifies if writing to the redo log is done

If it is On, even no logging operation in any segment in the tablespace is written to redo

It specifies if tablespace will be placed online after the creation

ON means datafile can be auto-extended. Off means no auto extension

It specifies if tablespace holds permanent or temporary objects

specifies storage parameters for all segments created in the dictionary-managed tablespace

specifies the minimum size for any extent in the tablespace

There are 2 types of space management

LOCALLY MANAGED Locally managed tablespaces manage all extent allocations in the datafile header using a bitmap. The advantages of using locally managed tablespaces are less space in system tablespace (extent management not saved in the data dictionary), reduced contention on data dictionary tables, eliminate the need to coalesce free extents

DICTIONARY-MANAGED Dictionary-managed tablespace records all extent allocations in the data dictionary tables.

One can specify the EXTENT MANAGEMENT clause in the CREATE TABLESPACE command and specify the management type (DICTIONARY/LOCAL).

If the LOCAL option is selected, one can specify the extent size using the UNIFORM SIZE n Kb/Mb clause. The extent size and allocation table are kept in the datafile header.

Altering storage parameters for locally managed tablespace is not allowed. To change storage parameters, one needs to create a new tablespace with new storage parameters and move all segments to the newly created tablespace.

Segment Space management

There are two options

Manual

Manual, you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, free lists, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

MANUAL is the default.

Auto

AUTO enables the use of bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management

ENCRYPTION ENCRYPT | DECRYPT

Use this clause to specify whether to create an encrypted or unencrypted tablespace. If you create an encrypted tablespace, then Transparent Data Encryption (TDE) is applied to all data files of the tablespace.

See also How to migrate Oracle database from Non ASM to ASM storage

Specify ENCRYPT to create an encrypted tablespace. Specify DECRYPT to create an unencrypted tablespace.

If you omit this clause, then the value of the ENCRYPT_NEW_TABLESPACES initialization parameter determines whether the tablespace is encrypted upon creation.
Before issuing this clause, you must already have loaded the TDE master key into database memory

Various Examples of Create Tablespace statements/oracle create Tablespace 19c

Permanent tablespaces

Permanent tablespaces are used to store user data and user-created objects like tables, indexes, and materialized views.

Single datafile
CREATE TEMPORARY TABLESPACE EXAMPLE DATAFILE '/u01/oracle/TEST/oradata/example_1.dbf' SIZE 1000M;
Multiple tempfile
CREATE TABLESPACE EXAMPLE DATAFILE
'/u01/oracle/TEST/oradata/example_1.dbf' SIZE 1000M
'/u01/oracle/TEST/oradata/example_2.dbf' SIZE 1000M;

Temporary tablespace in Oracle

Temp or temporary tablespaces are used to store data with a short lifespan (transient data), for example, global temporary tables or sort results.

Single tempfile
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M;
Multiple tempfile
CREATE TABLESPACE TEMP TEMPFILE
'/u01/oracle/TEST/oradata/temp_1.dbf' SIZE 1000M
'/u01/oracle/TEST/oradata/temp_2.dbf' SIZE 1000M
;

Undo tablespace in Oracle

Undo tablespaces are used to store “before image” data that can be used to undo transactions.

Single datafile
CREATE UNDO TABLESPACE UNDO_TBS1 DATAFILE '/u01/oracle/TEST/oradata/undo_1.dbf' SIZE 1000M;
Multiple datafile
CREATE UNDO TABLESPACE UNDO_TBS1 DATAFILE
'/u01/oracle/TEST/oradata/undo_1.dbf' SIZE 1000M
'/u01/oracle/TEST/oradata/undo_2.dbf' SIZE 1000M
;

Other examples

Tablespace created with extent management local of uniform size 1M and segment space management auto

CREATE TABLESPACE TEST DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 1000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created with extent management local of uniform size 1M and no automatic segment space management

CREATE TABLESPACE TEST DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 1000MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created with extent management local of Auto allocate size and no automatic segment space management

CREATE TABLESPACE TEST DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 1000M
EXTENT MANAGEMENT LOCAL Autoallocate;

Tablespace created with dictionary extent management

CREATE TABLESPACE TEST DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 1000M
EXTENT MANAGEMENT dictionary;

Tablespace created with local extent management and OMF with Oracle ASM diskgroups

CREATE TABLESPACE TEST DATAFILE '+DATA’ SIZE 1000M
EXTENT MANAGEMENT local;

File name is automatically managed by Oracle

Creating a Bigfile Tablespace
To create a bigfile tablespace, the syntax is similar, but you use the BIGFILE keyword:

CREATE TABLESPACE TEST DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 25G
AUTOEXTEND ON NEXT 2G MAXSIZE 400G
LOGGING;

Tablespace with encryption

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet_password"; CREATE TABLESPACE encrypt_ts DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 1M ENCRYPTION USING 'AES256' ENCRYPT;

How to verify the tablespace creation

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'your_tablespace_name';

Various Tablespace alteration options:

How to modify the existing Datafile auto extend characteristics in a Tablespace


you can modify the datafile auto-extend using the alter database datafile option

Syntax
ALTER DATABASE DATAFILE AUTOEXTEND ON|OFF NEXT MAXSIZE ;
Example
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE 2400M;

How to alter the tablespace to offline:

-Taking a tablespace offline/online is done using the ALTER TABLESPACE ‘name’ OFFLINE/ONLINE clause.
There are several options for taking tablespace offline:

NormalIt flushes all data blocks that belong to the tablespace from the SGA
TemporaryIt is used for tablespace point-in-time recovery (TSPITR)
ImmediateIt does not perform checkpoint or flush data blocks – requires media recovery
For recoverIt is used for tablespace point in time recovery (TSPITR)

How to alter the tablespace to read-only:

It allows read-only operations on tablespace (no DML), objects can be dropped from the tablespace

Syntax
ALTER TABELSPACE 'name' READ ONLY;
Example
ALTER TABELSPACE TEST READ ONLY;

Here is what happens when you state “alter tablespace read-only”

1) First the tablespace is put into a transitional read-only mode
2) Now the ALTER command waits for existing transactions to complete by committing or by rolling back. No further DML operations are allowed to the tablespace, and if a DML statement attempts further changes, then an error is returned.

The statement waits for the transactions that have pending or uncommitted changes to the tablespace and that were started before you issued the statement to either commit or rollback.

If the statement is hung, Here is what you can do
a) First you need to identify the transaction entry for the ALTER TABLESPACE…READ ONLY statement and displays its session address (saddr):

SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS AND SQL_TEXT LIKE 'alter tablespace%'; SQL_TEXT SADDR -------- ------ alter tablespace exampl read only 800473470

b) All transactions with smaller start SCN, which indicates an earlier execution, can potentially hold up the quiesce and subsequent read-only state of the tablespace.

SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB;
SES_ADDR START_SCNB
---------------- ----------------
800453470 7621 --> waiting on this txn
800467547 7623 --> waiting on this txn
800473470 7628 --> this is the ALTER TABLESPACE statement

You can now find the owners of the blocking transactions.

SELECT T.SES_ADDR, S.USERNAME, S.MACHINE
FROM V$SESSION S, V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY T.SES_ADDR
SES_ADDR USERNAME MACHINE
------------ -------------- ------------
800453470 USER1 MACH1
800467547 USER2 MACH2

We can contact both users and terminate the sessions.

See also Virtual IP Addresses : VIP in Oracle RAC

After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary, because no changes can be made to it.

How to drop the tablespace

Dropping the tablespace means tablespace is removed from the data dictionary, contents are removed from the data dictionary (optional), and data files are deleted (optional),

When the tablespace does not have any contents
Syntax
DROP TABELSPACE 'name';
Example
DROP TABELSPACE 'TEST';

When the tablespace have any contents Syntax
DROP TABELSPACE 'name' INCLUDING CONTENTS ;
Example
DROP TABELSPACE 'TEST' INCLUDING CONTENTS ;
When the tablespace have any contents and datafiles from OS also need to deleted
Syntax
DROP TABELSPACE 'name' INCLUDING CONTENTS and DATAFILES ;
Example
DROP TABELSPACE 'TEST' INCLUDING CONTENTS and DATAFILES ;

How to increase/autoextend the size of the tablespace

The increase can be done automatically or manually.

Automatically – using the AUTOEXTEND ON MAXSIZE n Kb/Mb clause.

Syntax
ALTER DATABASE DATAFILE AUTOEXTEND ON|OFF NEXT MAXSIZE ;
Example
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE 2400M;

Manually – ALTER TABLESPACE ‘name’ ADD DATAFILE ‘name’ clause or
ALTER DATABASE DATAFILE ‘DATAFILE NAME’ resize ;

Syntax
ALTER TABLESPACE ADD DATAFILE ;
Example
ALTER TABLESPACE TEST ADD DATAFILE '/u01/oracle/TEST/oradata/test_4.dbf' SIZE 1000M
ALTER DATABASE DATAFILE '/u01/oracle/TEST/oradata/test_4.dbf' RESIZE 2000M;

How to move tablespace/datafile to another location

In case of single or multiple data files move in a tablespace, steps are

ALTER TABLESPACE ‘name’ RENAME ‘file_name’ TO ‘file_name’

1) Alter tablespace TEST offline;
2) mv /u01/oracle/TEST/oradata/test_4.dbf /u02/oracle/TEST/oradata/test_4.dbf
3) ALTER TABLESPACE 'TEST' RENAME /u01/oracle/TEST/oradata/test_4.dbf ' TO ‘/u02/oracle/TEST/oradata/test_4.dbf ‘;
4) Alter tablespace TEST online;

In case of multiple data files of different tablespace, if we don’t want to bring all tablespace to offline, we can use the alter datafile command are

Alter database datafile ‘/u01/oracle/TEST/oradata/test_4.dbf’ offline; 
mv /u01/oracle/TEST/oradata/test_4.dbf /u02/oracle/TEST/oradata/test_4.dbf
ALTER database datafile RENAME /u01/oracle/TEST/oradata/test_4.dbf ' TO ‘/u02/oracle/TEST/oradata/test_4.dbf ‘;
Recover datafile ‘/u02/oracle/TEST/oradata/test_4.dbf ‘;
Alter database datafile ‘/u02/oracle/TEST/oradata/test_4.dbf’ online;

This move has become online from 12c onwards

How to create datafile/tablespace using OMF

OMF stands for Oracle managed file. It has the following features

a)Database files are easily distinguishable from all other files.

  1. b) Files of one database type are easily distinguishable from other database types.
  1. c) Files are associated with important attributes specific to the file type. For example, a datafile name may include the tablespace name to allow for easy association of the datafile to the tablespace, or an archived log name may include the thread, sequence, and creation date.

We need to set the DB_CREATE_FILE_DEST parameter in the database. Once it is set datafiles are created using OMF

Structure of OMF

///o1_mf_%t_%u_.dbf

Tablespace /datafile creation using OMF

CREATE TABLESPACE TEST size 800M;
Alter tablespace add datafile size 800M;

How to assign tablespaces to users

Users cannot create objects in a tablespace (even if it’s their default tablespace) unless they have a quota on it (or UNLIMITED TABLESPACE privilege).

Grant user PER access to use all space in the APPS_TX tablespace:

ALTER USER PER QUOTA UNLIMITED ON APPS_TX;

Dictionary views for Viewing Tablespace Information

Data dictionary for Oracle tablespace

Related Articles
Oracle tablespace documentation
Oracle documentation
How to Drop a datafile from Tablespace in Oracle
add datafile to tablespace :Check out this post on How to add datafile to tablespace in Oracle, add tempfile to temporary tablespace,how to add datafile in ASM
How to check Tablespace in Oracle : Learn about How to Check Tablespace in Oracle, tablespace free space,tablespace growth information,tablespace size ,associated datafiles ,tablespace size
shrink datafile in Oracle : Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
ORA-01652 :ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database.Check out how to resolve it