How i can change the database block size by setting the DB_BLOCK_SIZE parameter ???

Can I change the database block size?

Should I need to reboot instance after changing default block size?

When I should use large block size and when I should use small block size?

Here, I am going to share my knowledge about data block size in oracle database. I hope it will be helpful to you .

v The first question is: “Is it possible to increase or decrease the data block size?”

·         The DB_BLOCK_SZIE parameter specifies the default block size in database. The block size varies from 2 KB (2048 bytes) to 32 KB (32768 bytes) . The default block size is 8 KB (8192 bytes).

·       You can not change the database block size (DB_BLOCK_SIZE parameter) once the database is created in Oracle 8i and all earlier versions. Even you can’t create a new tablespace having different block size than default block size of the database. The only way to change data block size is to re-create entire database and change the DB_BLOCK_SIZE parameter in initialization parameter file.

·         However, the Oracle 9i & later versions allows you to use non-default block size. That is you can not change the default  block size of the database but you can create a new tablespace having different block size than default block size of the database by using “BLOCKSIZE”  clause while creating tablespace.

·         To create a tablespace having non default block size
§  Tablespace must be locally managed
§  You must have to configure memory buffer cache in the memory area (SGA) for that    block                        size. This can be configured by setting the DB_NK_CACHE_SIZE parameter in initialization                        parameter file
 §  The DB_NK_CACHE_SIZE parameter specifies the size of default buffer pool.
§  If you try to create the tablespace without configuring buffer cache for non default block size then                  “ ORA – 29339” error will be thrown.
 §  If the SGA is set and this parameter is not specified then default size of it 0. If the SGA is not set                    then the minimum size of this parameter must be 48 MB.

  For example, to create tablespace with non default block size (4k) in database whose default block size is 8k
             1.       Alter system set db_4k_cache_size=48m;
             2.         Create tablespace test datafile “D:\oracle\orcl\data_files\test.ora” size 200m blocksize 4k extent management local;

Thus, there is no need to bounce your instance to change data block size.

v  Now, answer to other question “ How to decide when to use large block size and when to use smaller one?”.

·         In data warehouse , larger data block size is more preferred as oracle can retrieve more data in single i/o operation.

·         In production database, smaller data block size is more efficient to minimize the contention for a specific data block due to frequent DML operations.

·         The tablespace with larger block size should be used to store indexes, tables having large number of rows and tables that requires frequent full scans.

·         The tablespace with smaller block size should be used to store tables having less records and tables that experiences frequent DML operations.

How the extents are allocated  in oracle database?

Hello friends , I hope you all are doing well. Today, I am going to discuss the extent allocation process in oracle database and the type of extent allocation. I hope it might be helpful to you.

       Let me explain how the allocation process works. When the request for a new extent is made that is when new data arrives for insertion, the oracle determines the datafile of that tablespace and then analyze the bitmap from datafile header in order to determine required numbers of contiguous data  blocks that can form an exetent. If that datafile does not have enough number of empty contiguous data blocks then it will look up in another datafile of the same tablespace. As  the enough number of contiguous data blocks are found the extent is allocated. If no enough contiguous data blocks are found ,the tablespace is supposed to be full.So,the extent is allocated only when you resize your tablespace or specify autoextend for it.

Now the question is what will be the size of allocated extent ?

The size of extent depends on the type of Extent management of the corresponding tablepsace and the type of Extent allocation.

 There are two possible types of extent management.

1). Locally Managed       2).Dictionary Managed

·         In the Locally managed tablespace the extent are managed by tablespace itself as a bitmap. That is, information about the extent allocation and dealloaction is stored in to data file’s header as a bitmap

·         In the Dictionary managed tablespace the extents are managed by data dictionary. That  is, information about the extent allocation and dealloaction is stored in to data dictionary tables rather than into header of  datafile.

The locally managed and dictionary managed tablespace have different modes of exetent allocation. There are two modes of extent allocation.

1)      Auto allocate                         2).Uniform

·         In the Auto allocate mode of extent allocation, the extent of size 64 KB is allocated

·         In the Uniform mode of extent allocation, the extent of size 1MB is allocated.

If the tablespace is Locally managed, the extent allocation may be Auto allocate or Uniform. If the tablespace is Dictionary managed, the extent allocation is always in  Auto allocate mode.

In the next post ,I will discuss about type of  Extent Management in detail.

Logical Standby Database and Physical Standby Database

What is difference between physical standby and logical standby Database?  

                                                         OR

How  physical standby and logical standby Database differs?

There can be two type of standby database.

                 1) Physical standby   2) Logical Standby

The Standby database is called “physical” if the physical structure of stand by exactly matches with stand by structure. Archived redo log transferred from primary database will be directly applied to the stand by database.

The Stand by database is called “logical”, the physical structure of both 
databases do not match and from the archived redo log we create SQL statements then these statements will be applied to stand by database.That is, Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby database.  Logical standby database permits you to add additional objects like tables, indexes, etc to the database.

The difference between physical and logical standby is the way in which changes from the primary database are applied. Both created as an exact image of the primary database. Both receive redo logs from the primary database. The difference is that a physical standby is mounted (but not open) and applies the received redo logs just as in the case of media failure recovery. A logical standby reconstructs SQL statements from the received redo logs and executes them and applies them when our A logical standby is (must be) opened.