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|
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.
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.
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|
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.