Oracle - SPLessons

Oracle Storage Structures

Chapter 7

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

Oracle Storage Structures

Oracle Storage Structures

shape Description

An Oracle database is comprised of logical and physical structures. Physical structures can be seen and performed on the working framework, for example the physical records that save information on a disc.

Oracle Storage Structures contain coherent structures that are performed and perceived by Oracle Database and are not known to the working framework. The essential sensible structure in a database, a table space, contains physical records. The database operator must comprehend the relationship between the physical and consistent structures of a database. The applications designer or client might know about of the coherent structure.

Oracle Physical Storage Structures

shape Description

The Oracle Physical Storage Structures is controlled by the operating  frame work documents that gives the conceptual physical stockpiling to the database.

One representative of an RDBMS is the autonomy of coherent information designs like views, index and tables from natural repository designs. Since natural and sensible designs are independent, to control natural storage of information beyond influencing access to coherent designs. For instance, In Oracle Physical Storage Structures changing the name of the database documents doesn’t change the tables name. An Oracle database is a collection of records that keep Oracle information in industrious circle stockpiling.

Physical Storage Structure

shape Description

In Oracle Physical Storage Structures, Based on the classification of files the Oracle database can be classified into the following types.

shape Conceptual

Control files

shape Description

Each database contain a control document or file. A control document contains sections that determine the natural design of the database. For instance, it holds the accompanying  data.

  • Database term
  • term and region of data files and redo log files
  • Time stamp about formation of database.

It will convolute the oversight records, i.e., at the same point of time it preserves a number of indistinguishable control of duplicate records, to ensure against a decline including the control file.

Each moment when an  occurrence of an Oracle database begins, its control records distinguishes the database and retry log documents that should be opened for database operation to continue. In the event that the physical cosmetics of the database is adjusted and control document is consequently altered by Oracle to reverse the alteration. A control record is additionally utilized as a part of database recuperation.

Data file structure

shape Description

It contain one or more physical information files. The information files subsist all the database information’s. The information of legitimate database design are indexes and tables, is naturally put away in the data files dispensed for a database. The qualities of data files are:

  • A dataset can be connected to a single database.
  • Data files contain qualities collections for consequently extend when the database come up short of space.
  • One or more data files structure a coherent unit of database stockpiling called a table space.

shape Conceptual

Information in a data set is perceived, as required, amid typical database application and put away in the collection store of Oracle. For instance, accept that a client needs to get to some information in a table of a database. In the way that the data is not same as the way that the data contained in the database, then it is used from the suitable data files and put away in consciousness.

Redo log files

shape Description

Each Oracle database has an arrangement contains more redo log records. The arrangement of redo log documents is altogether called as the re-try log for the database. A redo log is comprised of re-try sections.

The essential capacity of the re-try log is to report all progressions done to the information’s. If a break down restrict, alter information from being permanently recorded in the respective data files, then the progressions can be obtained from the redo log, so work is no way  vanished from the database.

Oracle Online Redo Log File Basics

shape Description

Oracle Online Redo Log Files empower the Oracle Server or DBA to redo exchanges, when the database break down occurs. It is the one of  reason to empower recuperation.

  • Exchanges are composed synchronously to the Oracle Online Redo Log Buffer in the System Global Area.
  • All database changes are composed to Oracle Online Redo Log to empower recuperation.
  • As the Redo Log Buffer fills, the substance are composed to Redo Log Files.
  • This incorporates uncommitted exchanges, fix section information,and outline/object administration data.
  • During database recuperation,data in Redo Log Files empower information that has not yet been composed to data files to be recouped.

shape Example

By viewing the below example the concept of redo log file can be easily understood.

  ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')
      SIZE 4M;
    TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo'); 

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, 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, we 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 logical storage structures to store the data like hierarchy and segments.

Logical Storage Hierarchy

shape Description

In Oracle Storage Structures, the logical storage hierarchy are 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

In Oracle Storage Structures, Segment are the 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 . The are 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 CREATE VIEW Cheapcars AS SELECT car_name from cars where cost 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 Storage Structures – They are the sensible storage’s used in oracle database.
  • Oracle Physical storage structure – Data stored in the database will be saved in disc folder made up of frame work system  blocks.
  • Oracle Logical Storage Structures – Contain table space, segments, extents.