Create a Tablespace in Oracle Database

How to Create a Tablespace in Oracle Database?

Tablespaces are the part of Logical Storage Structures in Oracle Database and are very different from the Physical Structure Components because of their purpose and the way they are managed. Albeit both Physical and Logical Structures are related to each other. DBAs normally have the knowledge of both the structures but usually they deal mostly with Physical Structures (Datafiles, Controlfiles and Redo logs). Tablespaces are something which is quite a Developer thing or the one who is going to use the segments inside the Tablespace to create his/her objects.


Anyway, creating tablespace inside Oracle Database is not a difficult task but before creating one make sure you have checked the following,

• Available Space on the Disk where you are going to Create the Tablespace
• Number of Datafiles you want to add within the tablespace
• The choice of other optional clauses like Autoextensible, Extent Management etc.

Below mentioned is the simple demo of Create Tablespace SQL Generation from Oracle Enterprise Manager which shows you the basic Syntax Oracle uses while creating Tablespace.

CREATE SMALLFILE TABLESPACE "TS1"
DATAFILE 'F:\DATABASE\ORADB11G\TS0101.DBF'
SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT NOCOMPRESS;

Note: In the above syntax, SMALLFILE Clause is used which is because of the available types of tablespaces in Oracle Database. It is mentioned there because I am creating the tablespace of SMALLFLE TYPE.

Now please choose any of the below mentioned examples to create your Tablespace,

SQL> create tablespace TS01 datafile 'F:\DATABASE\ORADB11G\TS0101.DBF' size 50M autoextend on;

Tablespace created.

Also you can add multiple datafiles in the CREATE TABLESPACE command. See below,

SQL> create tablespace TS01 datafile 'F:\DATABASE\ORADB11G\TS0101.DBF' size 50M , 'F:\DATABASE\ORADB11G\TS0102.DBF' size 50M autoextend on;

Tablespace created.

OR you can give different sizes for different datafiles (just for information, not a general case)

SQL> create tablespace TS01 datafile 'F:\DATABASE\ORADB11G\TS0101.DBF' size 100M , 'F:\DATABASE\ORADB11G\TS0102.DBF' size 50M autoextend on;

Tablespace created.

Let's try some other available options while creating Tablespace,

SQL> create tablespace TS01 datafile 'F:\DATABASE\ORADB11G\TS0101.DBF' size 50M autoextend on extent management local uniform size 5M;

Tablespace created.

Here I am specifying LOCAL Extent Management with uniform size of 5M. Extent Management is also Local by default and I will recommend you to use the same. Also if possible, let the Uniform Size be by default as well. The maximum size up to which datafile can grow is 32GBs so if you create a datafile with 200M size, the initial size of the datafile will be 200M only but it can be extended up to 32GBs. Use Autoextend On if you want to manage the size automatically or you can manually resize the datafile later.

I hope this helps !!