Oracle - SPLessons

Managing Space and Undo Data in Oracle

Chapter 19

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

Managing Space and Undo Data in Oracle

Managing Space and Undo Data in Oracle

shape overview

Managing Space and Undo Data in Oracle Database 12C consists of different strategies such as, every oracle database must keep up these conditions for maintaining the data that is used for roll back the changes in the database. That consisting of so many transactions, performed before commit statement and these are coherently refereed as undo.

Managing Space and Undo Data in Oracle Database 12C administer and monitor the undo data stored in the oracle database. And pro actively monitor the database space usages such as

Table space

A logical stockpiling container for segments is called table space. The 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.


A segments is a set of extents that contain all the data for a specific logical storage structure within a table space. For instance, for each table, oracle allocates one or more extents to form data segments.


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.

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&amp;lt;52640; 
ERROR 1054 (42S22): Unknown column 'name' in 'field list' 
sql&gt; CREATE VIEW Cheapcars AS SELECT car_name from cars where cost&amp;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).

Undo data

shape Description

Undo  records are used for
Managing Space and Undo Data in Oracle Database 12C contains Undo records and are used for making the undo changes that are performed in the database, when roll back statement is issued for transaction that are unconditional. Amid recovery of the database, undo records will undo the uncommitted changes applied from the redo log to the data files. It provides read consistency by maintaining the user records who are accessing the database information at the same time another client is altering it. Oracle database stores the before image of a data in an undo segment in the designated undo table space while performing the initialization parameters. Undo server many supports roll back to undo changes that were made by uncommitted transactions. Undo provides data read consistency in the ACID model and they are also very crucial in flash back operations.

ACID Model

Acid model contain the key features of relational databases that is associated with the accompanying values


A unit of work is said to be a transaction.In other words,a transaction is a sequence of related work and exchange of information maintained in a single unit.


An atomic activity is one that adequately happens at the same time in any programming part. An atomic activity can’t halt in the center it either happens totally, or it doesn’t happen by any stretch of the imagination. No reaction of an atomic activity are unmistakable until the activity is finished.


Consistent means that each client seems to be in consistency view about a data,that includes physical alterations done by the client individual transaction and other client transaction.


Each transaction should not be distributed to other sessions or other transactions.


Durability contains the entire durability components that are associated with each and every transactions.While functioning on nearby environment the durability of a transaction is said to be consistent.

Undo space management

shape Description

Managing Space and Undo Data in Oracle Database 12C, some elements of undo space management includes the facts that automatic undo management is enabled by default and have seen many automatic management references like automatic memory management, management of table space extents and auto extends. Cadillac features that helps oracle be the market leader as a relational database management system. So on behalf of oracle, automatic memory management take care of handling of undo space.

And at another stage one can verify auto extend is enables in the undo table space or not, and sizing the undo table space is going to be on our needs. And flash back query and flash back technology will describe how much will thing of the space management and describe how much safety measures are should be performed while altering the data in the database. And another key parameter that the administrator has to follow is about retention period, and it will keep the number of seconds that oracle will keep data in the undo table space that it contains and after it get expires then that data is likely to be boosted out of undo, and once the previous data image is out of undo table space that are not able to retrieve it through flashback technologies, so appropriate values are to be set, keeping in mind volatility because the more changes, the more DML that happens in the database and more space need to be allocate for the undo information.

And the undo information is stored in the undo segments. suppose user A is making a change, that stores in a segment, while another user is making a change, their data is stored in undo segment.And gives guarantee for certain period of time that data will be held in the undo area. Finally we have advisory framework, oracle has all these automatic management features and make suggestions of information constant in the disk space ans space usage in the system. And DB Express, is the internal embedded web management tool in oracle database 12c.

Managing Space

shape Description

Managing Space and Undo Data in Oracle, will managing table space contains such as

  • Multiple table space
  • Allocating table space to clients

Multiple table space

In Managing Space and Undo Data in Oracle, it Utilizing multiple table spaces to permits more adaptability in executing oracle database operations. At the point when a database has various table spaces. And can perform the accompanying tasks.

  • Individual table spaces must be backed up.
  • Distant information of one application from the information of another to keep numerous applications from being influenced if a table space must be taken offline.
  • Streamlining table space use by holding a table space for a specific sort of database use, for example,high overhaul movement, impermanent fragment stockpiling or read-just action.
  • Distant client information forms information word reference information to lessen I/O dispute.
  • Take individual table spaces disconnected from the net while others stay web, giving better general accessibility.
  • Keeps different data files of various table spaces on various disc drives to diminish I/O dispute.

Allocating table space to clients

Grant permissions to clients for making tables, materialized views, clusters, indexes, and different objects for creating the articles and a quota in the table space proposed to hold the item fragments.before creating a table space one need to create the database with necessary specifications. The SYSTEM table space is the main table space in the database. which contain data for functioning the server database. like system roll back segments and data dictionary. For creation of table space, system table space is the first table space. Like any other table space it can be able to manage, and higher level privileges should be required for controlling this table space.For instance, dropping or renaming the table space cannot be performed offline.The table space sysaux is the axillary table space for the system table space and get created when the database is create.

To make another table space, utilize the SQL articulations CREATE TABLE SPACE or CREATE TEMPORARY TABLE SPACE. And must have the CREATE TABLE SPACE frame work benefits to make a table space. Later, use the ALTER TABLE SPACE or ALTER DATABASE articulations to adjust the table space.ALTER TABLE SPACE or ALTER DATABASE system privilege can be contained in the oracle database.

And also utilize to CREATE UNDO TABLE SPACE articulations to create a uncommon kind of table space called an fix table space, which is particularly intended to contain fix records. These are records produced by the database that are utilized to move back, or fix, changes to the database for read consistency,recuperation, or as requested by a ROLL BACK explanations.


shape Key Points

  • Managing Space and Undo Data in Oracle – Maintaining the part of the data that is rollback.
  • Undo data – Oracle allows a DBA to allocate one active undo table space called as undo.
  • Undo space management – For creating clients quotes in oracle database resource manager for undo space.
  • Managing space – Deals with multiple table space and allocating client table space.