Oracle Database Physical and logical structures-04


Database Physical and logical structures


Smallest storage unit called a block. Size can be {2k,4k,8k,16k & 32k} – Default 8k.

Standard block and Nonstandard block. Default is standard block. Db_block_size =8192. Once it’s configured we cannot change this.

Each block has pctused, pctfree & high water mark.

PCTused – For insert – 40%

PCTfree – For Update – 10%

High Water Mark – Separate the used & free space of block


Multiple block size (9i onwards)

You can configure the size of the block for the database.

Db_nk_cache_size = nk

Db_16k_cache_size =16k

Block utilization parameter:


Initran  1 Initial transaction

Maxtran 256 – maximum transaction

Pctused – inserted records

Pctfree – Updated records




Every block has block header 1% and pctfree 10% pctused 40%, The remaining 49% depends on “pctused”. If it’s <40% then it will use 49+40 =89%. Grather than 40% will not use.

Eg: If u keep pctused to 40% and pctfree u can insert rows till 40 %.if the limit exceeds 40%,still also u can insert rows in the datablock till the limit reaches 80% (100%-20%) as u have kept pctfree to 20%.Now if one goes on deleting the rows,the block is not said to be free unless and until pctused falls below 40%.As soon as pctused falls below 40% from deleting the rows, that block can be used to insert the rows.In this way the cycle continous.So it is recommended that u never sum up pctused+pctfree=100.Always have some gap between them this helps in reducing ur Oracle server for allocation and disallocation of freelists.

Block level problems:

Row chaining

Row  migration

High water mark

Row chaining

The insert of data will create this. When the data is not fitting into a single block oracle will use more blocks depends on the data type size.

Row Migration

When there is an update in the already allocated data, which has no space left in the block will move the row completely into somewhere, which create a forward address.

High Water mark

The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, below is used one.

The delete command never resets the HWM level, truncate does.


The huge continuous of block is called extend. Default Extent size for system is 64KB. By default oracle will create a system auto allocation variable extent. In case if we create a data block with 16K or higher by default will be 1MB.  For uniform default is 1MB.


Default Storage parameter:






Minextent       1

Maxextent      256

Initran             100k – First extent

Next                200k – second extent size

Pctincrease     50% Next extent size {0% for uniform extent}


Oracle recommends the extent should be a uniform extent. Non-uniform will create a more fragmentation.

You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM).

If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice. It’s also good if it is not important for over space allocation and deallocation.

If you want exact control over unused space, then UNIFORM is a good choice.

Formula for third extent size:  Next + [next * pctincrease/ 100]

Ex: 200+(200*50/100) = 300 – Non-uniform extent

Ex: 200+(200*0/100) = 200 – Uniform extent

A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.

For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Ensure that each extent contains at least five database blocks, given the database block size. Temporary tablespaces that manage their extents locally can only use this type of allocation.

For system-managed extents, Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. If the tablespaces are created with ‘segment space management auto’, and if the database block size is 16K or higher, then Oracle manages segment size by creating extents with a minimum size of 1M. This is the default for permanent tablespaces.



A segment is a set of extents that contains all the data for a table within a table space.  A segment may or may not be contiguous on disk. The segments also can span more data files, but within table space.



To store a data like SQL table.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

43 + = 52