Sunday, November 25, 2012

2 TABLESPACE(1) : Tablespace types

Tablespace is the logical allocation unit to manage the actual database file on the file system. It is equivalent of the file group in Microsoft SQL Server.


Base on the Block size : Smallfile/Bigfile Tablespace

Before the 10g, each Oracle tablespace can have max to 1022 data file. Each data file has limit number of data blocks ( Platform dependent; typically 222 - 1 blocks). This become the limitation of the size of the tablespace.

From 10g, Oracle introduce the Bigfile tablespace. Each table space can have 232 blocks but only allow single data file.

Block Size Smallfile Storage Max Capacity Bigfile Storage Max Capacity
4K 16GB 16TB
8K 32GB 32TB
16K 64GB 64TB
32K 128GB 128TB

Base on Extent Management: Dictionary Management/Locally Management Tablespace

Oracle File allocation is base on the extend. Every time, when Database instance request the space, such as find the free space to insert data, it is looking for the free extend.  Extent is a continently data blocks.

There are 2 ways for manage the extend in the Oracle: Dictionary Management and Locally Management.

Dictionary Management

It is default extend management in 8i. Oracle track the used and free extend in the SYSTEM Tablespace. This eventually create the IO contention on the SYSTEM tablespace as whenever there is the space change, the oracle needs to update the SYSTEM tablespace.

create tablespace mytb1 datafile size 10M extent management dictionary;

In this example, I get the ORA-12913 error, that is because the SYSTEM tablespace is already local management therefore we can not create the dictionary management tablespace.


Locally Management

The “Locally” words mean the Oracle no longer track the free/used extent in the SYSTEM tablespace but “locally” in the tablespace itself.  Extent is a continently data blocks. There are 2 way to determine the extent size. UNIFORM and AUTOALLOCATE.

create tablespace mytb1 datafile size 10M extent management local UNIFORM size 1M;
create tablespace mytb2 datafile size 10M extent management local AUTOALLOCATE;


In the UNIFORM type, all extents in the tablespace are in the same size. In this example, all extents are 1MB.

In the AUTOALLOCATE type, the extent size is manage by Oracle server. The size is determine as below:

Number of extents Each Extents size size Total Size
1 ~ 16 extents 64K 16*64K=1MB 1 MB
17~79 1 MB 63*1MB = 63MB  64MB
80~199 8 MB 120*8MB=960MB 1024MB
200~ 64MB    

Base on Content: Temporary/UNDO/Permanent

  • Temporary Tablespace is used for store temporary objects and sort operations ..etc.
  • Undo tablespace is to replace the roll back segment before 9i. It is to rollback the un commit transaction, provide the consistent read for other user and flash back query.
  • Permanent tablespace is store the permanent objects such as USER tablespace and SYSTEM, SYSAUX tablespace.




  1. Rolex copia orologi offre una varietà di modelli, copia rolex orologi replica rolex sea dweller orologi dagli orologi professionali a quelli classici, per adattarsi a qualsiasi polso. Esplora la serie Rolex scegliendo il tuo modello, materiale, ghiera, quadrante e bracciale preferiti per trovare l'orologio adatto a te.

  2. I read this article, it is really informative one. Your way of writing and making things clear is very impressive. Thanking you for such an informative article. Oracle Fusion Cloud Manufacturing training Course



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates