Oracle - SPLessons

Oracle Logical Storage Structures

Chapter 10

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

Oracle Logical Storage Structures

Oracle Logical Storage Structures

shape Introduction

For Oracle Logical Storage Structures, Oracle Database allots logical space for all information in the database. The sensible units of space allocation in the database are segments, extents, extents, data blocks and table spaces. At the physical level, the information is put away in information documents on disk. The information in the data record is stored in operating system frame work blocks.

shape Conceptual

Storage Structure

shape Description

The main purpose of Oracle Logical Storage Structures is to execute and store programs. Ideally, if user want the programs and data to reside in the database permanently. However, this is not possible because the database is usually too huge to store all important data. So to over come this circumstances oracle database uses Oracle Logical Storage Structures to store the data like hierarchy and segments.

Logical Storage Hierarchy

shape Description

At the finest level of crude, Oracle Database stores information in data blocks. One legitimate data block relates to a particular number of bytes of natural platter space, for instance, 2 KB. Information block are the littlest units of capacity that the Oracle Database can utilize or dispense.

A extent is an arrangement of intelligently adjoining information blocks assigned for putting away a particular sort of data. In the 24 KB degree has 12 information blocks, while the 72 KB extent has 36 information blocks.

A portion is an arrangement of extents apportioned for a particular database item, like table. For instance, the information for the workers table is put away in its own information portion, while every record for representatives is put away in its own particular list fragment. Each database protest that expends stockpiling comprises of a solitary section.

Every segment owns one and only one table space. Hence, all extents for a portion are put away in the same table space. Inside the table space, a segment can incorporate extents from various information documents. For instance, single extent for a segment might be put away in client01.dbf, while another is put away in client02.dbf. A solitary extent can never traverse information documents. Allocating an extent to a particular case, the blocks are instantly apportioned to the free run down. In any case,if the extent is not permitted to a particular case, then the block itself will assigned just when the high water mark moves. The high water mark imprint the limit in the middle of utilized and vacant space as a part of a segment.


shape Description

Segment are collections of extents that consists of over all information about a consistent stockpiling design inside the table slot. For instance, one or more extents are allocated to Oracle Database to form the data segment and also one or more extents are allocates to form an index segment by the data base for a table. Oracle Database oversees segment space consequently or physically.

Client segments

shape Description

A database stores the single data segments for single client object. The segment are of various types. illustrations of client segments consists:

  • LOB or LOB partition
  • Index or index partition
  • Table, table partition, or table cluster

Every object partition and non-allotments object are saved in individual segment. For instance, there are five partitions consisting in index, then the index data contain five segments.

Creating client segment

shape Description

As the matter of course, the database utilizes conceded update only to database metadata in segment creation while constitute indexes and tables. The Release 2 of oracle database 11g (, the segment creation also concedes in the database while partitions are creating. While inserting the prime row into a table by the user, then the segments for the table or partition is created by the database, its indexes and its LOB columns.

Conceded segment enables creating you to abstain from utilizing database assets superfluously. For instance, an application can create large number of objects during installation, engrossing  disk space significant. And large portion of these objects might never be utilized. To manage segments for empty objects, utilize the DBMS_SPACE_ADMIN assortments. The Release 2 of oracle database 11g (, PL/SQL package can be utilized for the following:

  • For empty tables or partitions manually materialize segments doesn’t contain segments that are automatically created.
  • Expel segments between the vacant partition or tables that presently contain unfilled portion assign.

For illustrating, segment and object creation relationship can be assumed by conceded creation of segment as disabled and table creation will be as follows

Create table <test_table>(my column_number);

Test_table => Name of the table.

shape Conceptual

While creating a primary key with a table, automatically construct an index for this key by the oracle database. Again except that creation is impaired for conceded segment.

Create table lob_table(my_column number,primary key,clob column clob);

Lob_table =>Name of the table in the database.

Primary key => Is a constraint.

Clob column clob => Name of the column.

The data is stored in one segment for lop table, while the segment is different for the implicitly created index and also, it stores the club data in individual segment, because CLOB index is associated. Hence, creation of four different segments will be done by using create table.

shape Conceptual

Table space

shape Description

A logical stockpiling container for segments is called tablespace. Theare database objects are segments, like index and tables and the storage space that it consumes. At the physical stage, information in one or more data files will be stored in the table space.

shape Syntax

create view <table_name>as select <column_name> FROM <table_name> where <condition>;

table_name => Any accurate table.

column_name => The columns that inserted in the table.

Condition => The condition to get the specific result set . 

shape Examples

By viewing the below example, the concepts of table spaces can be understood easily.

sql&gt; select * from cars;
| car_id | car_name | cost   |
|      1 | audi     |  52642 |
|      2 | skoda    | 526400 |
|      3 | volva    |  52640 |
|      4 | volva    |  52000 |
|      5 | hummer   |  41400 |
5 rows in set (0.00 sec)

sql&gt; create view cheapcars as select name from cars where cost&lt;52640; 
ERROR 1054 (42S22): Unknown column 'name' in 'field list' 
sql&gt; CREATE VIEW Cheapcars AS SELECT car_name from cars where cost&lt;52640; 
Query OK, 0 rows affected (0.13 sec) 
sql&gt; select * from cheapcars;
| car_name |
| volva    |
| hummer   |
2 rows in set (0.16 sec)

In the above example, a simple table cars has been created and by applying view operation to that table, like inserting some condition on column name.(i.e.,cost of car<52640 is the condition, and it show the cost of cars less than that value).  

shape Conceptual

Permanent table space

shape Description

A persistent schema objects are permanent table space groups. The table space containing segments for objects are stored in the information files.

A default permanent table space is assigned to each client in the database. The default SYSTEM and SYSAUX table spaces are used only by small databases. Hence, at least one table space for application data and to store user will be created by oracle. By using the  table spaces will accomplish the succeeding goals.

  • Allots an amount to a database clients.
  • Control disk space designation for database information.
  • Make a transportable table space that you can duplicate or move from one database to another, even across the stages.
  • Perform reinforcement and recuperation of single tables paces.
  • Take individual table spaces online or offline without affecting the availability of the whole database.
  • Import or fare application information by utilizing the Oracle Data Pump utility.

System table space

For administrative table space, System table space is necessary. When it is created it get included in the database.To manage this oracle database use system database.

The following information will be included in the SYSTEM table space, holds by the SYS client:

  • Incorporated stored objects like procedures, triggers and packages.
  • Tables and views that contain administrative data about the database.
  • The information word reference.

Any other table space will be managed by System table space, yet requires a more elevated amount of benefits and is limited in a few ways. For instance, the SYSTEM table space can’t  be drop or rename. All newly created clients table spaces will be locally managed by oracle database. In a database with a locally managed SYSTEM table space, you cannot create dictionary-managed table spaces. Hence, execute the create database statement manually and accept the dictionary managed space in the SYSTEM table and can move the current word reference SYSTEM table space to a privately group space.

SYSAUX table space

The auxiliary table space to the SYSTEM table space is SYSAUX table space. Centralized location for database metadata will be provided by SYSAUX table space, the SYSTEM table space doesn’t reside at it. It table spaces created by the database will be reduced by default, both in the user-defined databases and in seed database. A few database segments, including Oracle Streams and Oracle Enterprise Manager utilize the table space SYSAUX as default stockpiling area. In the manner, the SYSAUX table space is made naturally amid upgrade or creation in the database.


shape Description

In Oracle Logical Storage Structures, the logical unit of database storage space allocation made up of contiguous data blocks are called as extents. Extent containing the data blocks are legitimately coterminous however it can be expand out on disc as a result file system implementations and raid striping.

Assigning the underlying extent for a information fragment while the segment is made in the database. It constantly contained in one information document. Instead information is not combined to the segment, the information blocks in the underlying degree of extent are allotted for this segment solely. The primary data block contains the segment for each directory belongs to  extents in the segment.

shape Conceptual

If primary extent turn out to be full, and required more space, it allocates automatically an incremental extent for this segment by the database. An incremental extent consequent made to this portion. The designation calculation relies on upon whether the table space is privately overseen or word reference oversees. In the privately oversaw case, the database looks the bitmap of an information document for nearby free pieces. In the event that the information document has lacking space, then the database looks in another information record. Degrees for a fragment are dependably in the same table space yet might be in various information records.

The database will assign extents for a segment for any information record in the table space. For instance, the portion can apportion the underlying degree in user1.dbf, dispense the principal incremental degree in users2.dbf, and spread the accompanying degree in user1.dbf.


shape Key Points

  • Oracle Logical Storage Structures – Contain table space,segments,extents.
  • Segments – A segment is a set of extents allocated for a table.
  • Table space – Logical storage unit which divides the database.
  • Extents – Specific number of contiguous data blocks.