Introduction to Oracle DML statements and undo

For DML statements, as long as the data block is modified, the Oracle database retains the modified data in the undo segment, while the undo segment is saved in the Undo table space. From Oracle 9i, there are two ways to manage undo: Automatic Undo Management (Automatic undo Management, abbreviation AUM), and manual undo Management (Manual undo Management, referred to as mum). Oracle 9i can only use mum, and in mum, undo segment is also called rollback segment. Oracle has recommended the use of AUM from Oracle 9i, instead of using mum. Therefore, we do not discuss mum and rollback segment.

1. DML statements and Undo

When we emit a DML (such as the update T set col1= ' A ' where col1= ' B ') statement, the execution process can be roughly summed up in the following steps.

Parse in the shared pool to generate an execution plan. The specific analytical process is shown in chapter 5th.

Assuming that according to the execution plan, the records of col1= ' B ' are stored in the number 54th data block of the No. 10th data file.

Server process in buffer cache to find an available undo data block, if not found, then go to the undo table space to find an available undo block, and transfer into the buffer cache. Suppose you get the Undo data Block number 24th, which is located in the number 11th undo data file.

The value before the change, that is, a put in the number 11th undo data block.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

As the undo data block changes, a redo record is generated, assuming the redo record number is 120.

Line number transaction ID file# block# Row column value

T1 col1 A

Find the number 54th block in buffer cache. If it is not found, it is transferred from the number 10th data file.

Will change the value, that is, b into the number 54th data block.

Because the data block has changed, it produces a redo record, assuming the redo record number is 121.

Line number transaction ID file# block# Row column value

121 T1 col1 B

Control is returned to the user, and if DML is performed in Sql*plus, the cursor returns.

When the user issues a commit, the LGWR process is triggered, the 120 and 121 redo records are written to the online log file, and the transaction status markers recorded in the header of the 54th data Block and the 11th undo data Block are set to committed. Control is then returned to the user, and if the DML operation is performed in the Sql*plus, the cursor returns.

At this point, the number 54th block and the number 11th undo block are not necessarily dbwn written to the data file. Only if the number of dirty blocks reaches a certain level will it be written.

The undo block used by the transaction can be overwritten as long as it is committed or rolled back. For the above example, when the user issues a commit, the data in the 11th undo block can be overwritten by other firms.