LOGICAL & PHYSICAL STRUCTURE OF DATABASE

 

       BLOCK
Block is smallest storage unit into which we can store data is called block.
Block size is 8kb.
„Ï 1 Byte = 8 Bit 1024 MB = 1 GB 2k Windows
„Ï 1 Kb = 1024 Bytes 1024 KB = 1 MB 4k Solaris
„Ï 1 Mb = 1048576 Bytes 8k Linux
„Ï 1 Gb = 1073741824 Bytes 16k Hp ¡V UX
# Mount show all mounted files. 32k AIX
# df ¡Vk (show free space in all TS)
There are two phases in block
„Ï One is for feature usage. We call it as pctfree.
„Ï One is for current usage. We call it as pctuse.
Block has two STORAGE PARAMETER called pctfree and pctuse.
Pctuse: it is the amount of space reserved for session.
Pctfree: it is the amount of free space left in the block for feature updating.
A database block is a combination of OS blocks. Suppose if the size of the OS block
is 4kb then 2 OS blocks combined to form database block.
„Ï Up oracle 9i minimum block size as 2k.
„Ï At oracle 10g minimum block size as 8k.
Physical structure
Collection of data files in operating system is called physical structure. Or, Set of
operating system files in database.
Logical Structure
The logical structure of the oracle architecture how the physical space of a
database is to be used.



In create table – table name is table segment
Tablespaces: A tablespace may consist of one more segments.
Segments: A segment made up of extents.
Extents: An extent is made up of blocks
Block: A block is the smallest storage unit of read and writes operations.
STRUCTURE OF A BLOCK
Header
Contains information regarding the types of block (table, block, index etc)
transaction information regarding active and post transaction on the block.
Table Directory
Contains information about the tables that store rows in this block.
Row Directory
Contains information describing the rows that are to be found on the block. This
is an array of pointers to where the rows are to be found in the data portion of the
block.
Block Over Head
The three above pieces are known as the block overhead and are used by oracle
to manage the block itself.