99 - [Oracle 19C] Managing UNDO Data

 

 

Managing UNDO Data

EMP table
empid	sal
100	5000

UPDATE EMP SET SAL=6000 where empid=100;

 

• The Oracle Database server saves the old value (undo data) when a process changes data in a database.

• It stores the data as it exists before modifications.

• Retained at least until the transaction is ended.

 

UNDO Data used to support :
  Rollback operations
  Read-consistent queries
  Oracle Flashback Query, Oracle Flashback Transaction, and Oracle Flashback Table
  Recovery from failed transactions

 

 

A failed transaction occurs when a user session ends abnormally (possibly because of network errors or a

failure on the client computer) before the user decides to commitor roll back the transaction.

Failed transactions may also occur when the instance crashes or you issue the SHUTDOWN ABORT

command.

 

How the transaction ends?
   User undoes a transaction (transaction rolls back).
   User ends a transaction (transaction commit).
   User executes a DDL statement, such as a CREATE, DROP, RENAME, or ALTER statement.
   session terminates abnormally (transaction rolls back).
   User session terminates normally with an exit (transaction commits).

 

 

 

Transactions and Undo Data

Transactions and Undo Data

   Each transaction is assigned to only one undo segment.
   An undo segment can service more than one transaction at a time.
   When a transaction starts, it is assigned to an undo segment. Throughout the life of the transaction, when data is changed, the original (before the change) values are copied into the undo segment. You can see which transactions are assigned to which undo segments by checking the V$TRANSACTION dynamic performance view.
   Undo segments are specialized segments that are automatically created by the database server as needed to support transactions. Like all segments, undo segments are made up of extents, which, in turn, consist of data blocks. Undo segments automatically grow and shrink as needed, acting as a circular storage buffer for their assigned transactions.
   Undo information is stored in undo segments, which are stored in an undo tablespace.
   you cannot create other segment types, such as tables, in the undo tablespace

 

 

 

show user
show con_name
/*
USER is "PDB10_ADMIN"
CON_NAME 
------------------------------
PDB10
*/


create table emp( n number, sal number );
/*
Table EMP created.
*/


insert into emp values (1,500);
insert into emp values (2,400 );
/*
1 row inserted.
1 row inserted.
*/


select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
--USED_UBLK = Number of undo blocks used 
--USED_UREC = Number of undo records used
/*
       SID    SERIAL# USERNAME         USED_UREC  USED_UBLK
---------- ---------- --------------- ---------- ----------
       408      13002 PDB10_ADMIN              2          1
*/


insert into emp values (3,700 );
/*
1 row inserted.
*/


select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
/*
       SID    SERIAL# USERNAME         USED_UREC  USED_UBLK
---------- ---------- --------------- ---------- ----------
       408      13002 PDB10_ADMIN              3          1
*/


commit; --this mean end of transaction
/*
Commit complete.
*/


select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
/*
no rows selected
*/