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.