Overview to Oracle Database Storage Structure - A complete explanation guide to Storage Structure used in Oracle Database
Understanding physical and logical storage structures in Oracle Database? What are types of Physical and Logical Storage Units in Oracle?
Oracle Database Storage Structure is divided into 2 parts,
LOGICAL STORAGE STRUCTURE:
Oracle Database uses the logical structure to store the data in the form of tablespaces, segments, extents and blocks.
As you can see in the image above, the logical structure goes like,
Oracle Data Block -> Extent -> Segment -> Tablespace
Oracle DB/Data Block is the smallest storage unit used in Oracle Storage Architecture i.e. the complete database is the combination of hundreds and thousands of DB Blocks. The available size limits for Oracle DB Blocks is completely OS dependent and is in the range of 2KB (minimum), 4KB, 8KB, 16KB, 32KB (maximum).
An EXTENT contains contiguous data blocks allocated for data storage. An Extent is basically a collection of DB Blocks so a 72KB Extent with 4KB of DB Block will be having 18 DB Blocks. Similarly if the DB Block size is 2KB then a 72KB Extent will be having 36 DB Blocks. Due to the size limits, an extent can normally store a piece of information let's say few rows of a table.
A Segment is the collection of Extents normally used to store a DB Object like a Table or an Index. So lets say if you create a table named ABC in the database, it'll be having it's own segment with the same name and the data for the same object will be kept in it's segment.
A tablespace comprises of one or more segments and is normally used to store multiple objects for 1 or more schemas (depending upon the architecture).
PHYSICAL STORAGE STRUCTURE:
Physically Oracle Database stores all the data in datafiles. As simple as it is. There are a lot of other files like controlfiles, redolog files, archivelog files, parameter file but the main DB Files are datafiles which contains the data which you store in the database. Other files are used for Oracle Database working apart from REDO LOG or ARCHIVE LOG as they also store the data but in the form of query. Redo Logs stores the updates getting performed in the database, or to make it more simpler, stores the DML queries getting executed in your environment.
In the datafiles, there are also different varieties like the datafiles which permanently holds the data, datafiles which are used for temporarily for sorting purposes and datafiles which are used to hold the undo data till the transaction gets committed.
More on this will be updated soon !!
Understanding physical and logical storage structures in Oracle Database? What are types of Physical and Logical Storage Units in Oracle?
Oracle Database Storage Structure is divided into 2 parts,
- Logical Storage Structure
- Physical Storage Structure
LOGICAL STORAGE STRUCTURE:
Oracle Database uses the logical structure to store the data in the form of tablespaces, segments, extents and blocks.
Oracle Data Block -> Extent -> Segment -> Tablespace
Oracle DB/Data Block is the smallest storage unit used in Oracle Storage Architecture i.e. the complete database is the combination of hundreds and thousands of DB Blocks. The available size limits for Oracle DB Blocks is completely OS dependent and is in the range of 2KB (minimum), 4KB, 8KB, 16KB, 32KB (maximum).
An EXTENT contains contiguous data blocks allocated for data storage. An Extent is basically a collection of DB Blocks so a 72KB Extent with 4KB of DB Block will be having 18 DB Blocks. Similarly if the DB Block size is 2KB then a 72KB Extent will be having 36 DB Blocks. Due to the size limits, an extent can normally store a piece of information let's say few rows of a table.
A Segment is the collection of Extents normally used to store a DB Object like a Table or an Index. So lets say if you create a table named ABC in the database, it'll be having it's own segment with the same name and the data for the same object will be kept in it's segment.
A tablespace comprises of one or more segments and is normally used to store multiple objects for 1 or more schemas (depending upon the architecture).
PHYSICAL STORAGE STRUCTURE:
Physically Oracle Database stores all the data in datafiles. As simple as it is. There are a lot of other files like controlfiles, redolog files, archivelog files, parameter file but the main DB Files are datafiles which contains the data which you store in the database. Other files are used for Oracle Database working apart from REDO LOG or ARCHIVE LOG as they also store the data but in the form of query. Redo Logs stores the updates getting performed in the database, or to make it more simpler, stores the DML queries getting executed in your environment.
In the datafiles, there are also different varieties like the datafiles which permanently holds the data, datafiles which are used for temporarily for sorting purposes and datafiles which are used to hold the undo data till the transaction gets committed.
More on this will be updated soon !!
No comments:
Post a Comment