How To Create Tablespace On Oracle

Tablespaces are areas dedicated to storing objects in the database.

Tablespaces are logical constructs. However, it contains physical data files or temp files.

There are three different tablespace types on Oracle.

1. Permanent Tablespace
2. Temporary Tablespace
3. Undo Tablespace

Permanent Tablepace:

Permanent tablespaces are the types of tablespaces used to hold data in a database. There are two kinds of permanent tablespace.

• bigfile tablespace
• smallfile tablespace

Bigfile Tablespace:

There is only one data file or temp file in the bigfile tablespace.

If the block size is 32K, the maximum size of this data file or temp file can be 128TB.

If the block size is 8K, the maximum size of this data file or temp file can be 32TB.

EXTENT MANAGEMENT DICTIONARY can not be used on bigfile tablespaces.

Smallfile Tablespace:

The oracle tablespaces we know. You can create 1022 data files in a smallfile tablespace.

If the block size is 32K, the maximum size of this data file or temp file can be 128GB.

If the block size is 8K, the maximum size of this data file or temp file can be 32GB.

Below you can see examples and explanations about creating a tablespace.

Example1(Smallfile Tablespace):

In the following example, we create a smallfile tablespace containing 9 data files.

The meanings of the parameters in the script can be found in the following table.

TABLESPACE_NAME	You should write the name of the tablespace here.
DISKGROUP_NAME	You should write the diskgroup name here that you want to keep the datafile in ASM.
1024K	Datafile’s initial size
AUTOEXTEND ON	We set datafile to grow automatically.
NEXT 100M	We specify that each automatic growth will be 100M.
MAXSIZE UNLIMITED	We set the datafile’s maximum size to unlimited. But as I mentioned earlier, small datafiles can grow up to 128GB even if you set MAXSIZE to UNLIMITED.
LOGGING	This specifies that each object that will be created in the tablespace will be in LOGGING mode by default. For details, you can read the article “What is  logging_clause On Oracle(LOGGING or NOLOGGING)“. This parameter is not available for Temporary and Undo tablespaces.
ONLINE	You can set the tablespace to be online or offline using the ONLINE or OFFLINE parameters. Can not be used for Temporary tablespace.
EXTENT MANAGEMENT LOCAL AUTOALLOCATE	Extent Management determines how extents are managed. If you specify LOCAL, it is managed locally. If you specify DICTIONARY, extent management is managed using dictionary tables. In Oracle 9i, LOCAL becomes default. We recommend using it this way. For details, you might want to read the article titled “How To Check If There Is a Dictionary-Managed Tablespace On Oracle Database“. By specifying AUTOALLOCATE, we specify that the extent management process will be managed automatically.
BLOCKSIZE 32K	This means that the Tablespace’s Block size is 32K.
SEGMENT SPACE MANAGEMENT AUTO	This statement can only be used for permanent tablespaces that have the extent management parameter defined as local. You can monitor the used and empty fields in the segments with this parameter. If you set it to AUTO, ORACLE will do it for you. We recommend using it this way.
FLASHBACK ON	The FLASBACK parameter can be set to ON or OFF. Default is FLASBACK ON. This way, this tablespace works in FLASHBACK mode. For details you can read the article “What is FLASBACK DATABASE and How To FLASHBACK Oracle Database“.

Example2(Bigfile Tablespace);

In the following example, we create a bigfile tablespace containing 1 data file.

The meanings of the parameters in the script can be found in the table above.

Example3(Temporary Tablespace):

You can create an example temp tablespace with the following script.

UNIFORM SIZE 1M

1 Mb’lık Uniform Extenler olacağını belirtir.

In some cases, you may need to move the Temp Tablespace to another diskgroup.

To do this online, I recommend you read the article “How To Move The Temp Tablespace to Another Diskgroup On Oracle“.

Example4(UNDO Tablespace):

If Undo Management is done automatically in the database, a tablespace will be assigned automatically for undo.

Therefore, if the database is in automatic undo management mode, undo tablespace is not created manually.

If the database is not in automatic undo management mode then SYSTEM Rollback segment is used.

In such a case, you can manually create an undo segment using the following script.

But I insist that you get the database into automatic undo management mode.

RETENTION NOGUARANTEE

If you use the RETENTION NOGUARANTEE statement, unexpired data in undo segments can be used by subsequent transactions. In some cases some queries may take snapshot too old error. You can find the details in the article titled “ORA-01555 Snapshot Too Old“.