Sunday, November 25, 2012

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

SELECT
  DT.TABLESPACE_NAME,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT.ALLOCATION_TYPE
FROM
  DBA_TABLESPACES DT
image  

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.

image

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;

image

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.

 

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm

0 comments:

Post a Comment

 

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