OCP knowledge point explanation-Undo rollback and submission

I. Summary of the use of rollback segments: 1.1 Seq Sequence Value: Step 1: Each rollback block header has a SEQ value. When the transaction is extended to the new area, the SEQ in the new area is the SEQ plus 1 in the previous area. For example:
Www.2cto.com Step 2: Step 3: Transaction B commit Step 4: transaction A releases A new update statement, which occupies the Rolling Block and starts to grow: transaction A grows, it occupies 83, 84, 85, 86, and 87 blocks, but when it reaches 88 blocks, it is found that the sequence values of 88 and 89 blocks are 10, not smaller than their own sequence values, transaction A skips 88 and 89 blocks. The Sequence Value of 90 blocks is less than 10, and transaction A occupies 90 blocks. Step 5: transaction A continues to grow. When transaction A extends to the next partition, the sequence value of the next block is 11: conclusion: only when the sequence value allocated for a transaction block is greater than the Sequence Value of the block itself can the transaction occupy this block. In the previous example, transaction B has committed the block. It will be occupied only when another transaction is committed from the end zone to Zone 0. Extend of www.2cto. com1.2: transaction A continues to grow and has occupied zone 0. Transaction A requests A new rollback block. As transaction Z starts from Zone 1, Zone 1 has an "active transaction". At this time, Oracle allocates A new zone as Zone 1, the old zone 1 is changed to zone 2, as shown below: even if there is still an idle old zone 2 after the old Zone 1, the new zone 1 will still be allocated here. 2. rollback and commit: 2.1 rollback: if a transaction is rolled back, the operation steps are as follows: 1. retrieve its DBA column in the transaction table. This DBA is the DBA2. find the end of the rollback chain through this DBA. take the DBA, row number, column number, and pre-image information of the original table corresponding to the rollback record from the beginning along the rollback chain. 4. rewrite the previous image information to the DBA of the original table. As shown in the preceding steps, the rollback order is the same as the operation order. If you delete 1, 2, 3, 4, five Records, and then rollback. Oracle inserts 5, 4, 3, 2, and 1 records from the back and forward. 2.2 commit: after the transaction is committed, the Transaction Status column in the transaction table is set to non-active. This transaction cannot be rolled back, because the rollback must first go to the transaction table to find the end block of the transaction rollback chain. However, after the transaction is committed, although the transaction table indicates that the transaction is a non-active transaction, but the information in the rollback block is still there, although it cannot be rolled back, however, the information in these rollback blocks can still be read before being overwritten. In this regard, we can find a transaction, commit the transaction, and then DUMP the rollback block it occupies. According to the rollback segment cycle usage principle, these rollback records will be overwritten only when they are in the next loop. 3. Create a CR block by yourself: 3.1 query magic: Step 1: publish the following statement in session 17: 17> var x refcursor17> exec open: x for select substr (c, 1, 5), id from t8; PL/SQL process completed successfully. Step 2: delete all records of T8 in session 13 and submit: 13> delete t8; delete 10 rows. 13> commit; the submission is complete. Step 3: Output all rows of cursor X in session 17: 17> print xSUBSTR (C, 1 ID ---------- g 7 h 8i 9J 10AA1B 2C 3D 4E 5f 6 10 rows have been selected. We have deleted and submitted in session 13, but in session 17, all rows in T8 are displayed. Why? 1) Open the cursor in step 1, but it is not executed and executed at this time. In the PGA of session 17, a memory will be opened to store the cursor. In the cursor information, one of them is the SCN that the cursor opens. If the SCN is 1000 at this time. 2) in step 2, perform operations on the table T8 and submit the table. The header of each data block in Oracle records the current SCN value of this block, which changes with the update of the block. If the SCN value is 1200 when we update the T8 table in step 2, the value will be recorded in the SCN value of the block header. 3) in step 3, we release the Print x statement, which completes the execution, crawling, and other steps. When a row is crawled from the related blocks of the T8 table, Oracle compares the SCN when the cursor is opened with the SCN of the block itself. If the latter is found to be greater than the former, it is proved that the cursor is opened before the table is updated. Oracle will find information smaller than the SCN In the rollback segment and construct a block before the cursor is opened, this is the CR block. If the information for the T8 table in the rollback segment has been overwritten, an ORA-01555 snapshot is too old. This error is sometimes returned when the query is executed for a long time. 3.2 SCN: SCN is an internal timing value of Oracle, that is, the internal clock of Oracle. After 9i, we can obtain the current SCN number through the following method: scott @ MYTWO> select scn, to_char (scn, 'xxxxxxxx') from (select dbms_flashback.get_system_change_numberSCN from dual ); SCN----------8727805 in almost all places, you can see SCN. In transaction tables, rollback records in rollback blocks, data blocks, log files, and so on, we have mentioned that in the cursor, records also contain the SCN when the cursor is opened. 3.3 construct CR blocks: Oracle will read the rollback segments in three cases to construct CR blocks: 1. Oracle will construct CR blocks as long as there is a lock on the data blocks. 2. the SCN of the cursor is smaller than the SCN of the block. It indicates that the block has been modified after the cursor is opened. This requires the CR block to be constructed. 3. In the flash query, if the requested SCN is smaller than the block SCN, the CR block must also be constructed. Step 1: first observe the row distribution of table T8: ROWIDBLOCK # CID ------------------ ---------- ------------ AAAB3LAAFAAAf/mAAA 131046 a release/mAAB 131046 B release/mAAC 131046 c Release/nAAA 131047 d release/nAAB 131047 e release/nAAC 131047 f release/oAAA 131048g 7AAAB3LAAFAAAf/oAAB 131048 h 8AAAB3LAAFAAAf/oAAC 131048 I 9AAAB3LAAFAAAf/pAAA 131049 j10 Step 2: run the update command 13> update t8 se. T c = upper (c) where id <= 2; two rows have been updated. 13> commit; Submission completed .... 10> update t8 set c = 'az' where id = 1; 1 row updated. 10> update t8 set c = 'by' where id = 2; 1 row has been updated. At this time, the SCN is 18> select dbms_flashback.get_system_change_number SCN from dual; The SCN----------8729612 summarizes: changes in Row 1 a-> A-> A1 A2 B2-> aa1-> aaa1aaaa1-> aaaaa10-> changes in Row 2 of az B-> B-> B2-> bb2-> bbb2bbbb2-> bbbbb10->, first, let's talk about the structure of the CR block at the end of the commit: Step 3: Structure of the CR block at the end of the commit: Next we modify the above. If we publish a statement to query the T8 table in session 16: 16> select * from t8 where id <= 3; not submitted after the last modification. If there are other session access rows 1 or 2, the rollback operation will take place, to construct a CR block. Let's roll back and feel what Oracle rollback has done: DUMP block 131046: alter system dump datafile 5 block 131046; in the transaction header, there is a structure like this: Itl XidUba FlagLckScn/Fsc0x01 0x0018. 008.20.001a0x03800073.000e.02 ---- 2fsc 0x0000.000000000x02 0x0019. 007.000000200x03800053.003700001c --- 0scn 0x0000. 008533f6 it is called the ITL, Interested Transaction List, and the List of related transactions. Each row in this table is also called a SLOT according to Oracle's conventional statement. When a transaction modifies the data in this block, or a transaction occurs in this block, it occupies a slot in ITL. Next, let's take a look at the information in ITL. The first column contains two ITL numbers, 0x02 respectively. As the number of transactions in the block increases, the number of slots increases. The second column, Xid, transaction Xid number, and Xid are divided into three sections. As mentioned above, rollback segment number, slot number, and serial number. Let's take a look at the 0x01 transaction, which occupies 8th slots of the rollback segment on the 24 th. The serial number of this slot is 26, that is, 24.8.26. The third column, UBA, rolls back the block address. It is also divided into three sections, namely, the rollback block address, the rollback block serial number, and the last record number (that is, the irb information ). Here, 0x01 transactions occupy files No. 14, rollback blocks No. 115, and Block Serial Number 14. The last record of the rollback chain is at 115 of the records. The fourth column, Flah, indicates that there are four digits in total, and the four digits are "----". The transaction is committed at the end. The first digit is C and "C ---". The transaction has been committed. The Fifth Column, Lck, indicates the number of rows modified by the firm. These rows are all locked by transactions. Column 6, SCN/Fsc, is the SCN information of the transaction. 0x01 transactions are committed at the end, so the column is 0. The new block number is still 131046. In the memory, its HASH location is in the same HASH Cache Buffer Chain as the original 131046. It is a CR block of 131046. The structure of the CR block has not been completed yet. We replaced the current row value with the row value of the front image. We updated the last step and only modified the row value, as we have mentioned above, each block header has an ITL, which initially has two slots. Each transaction committed at the end occupies one slot and can be expanded as the number of transactions increases. If the transaction has been committed, the slot it occupies will be overwritten by the next transaction. Therefore, the last update we made in session 10 will cover an ITL slot of 131046 of the initiated transactions, and the covered ITL slot will be recorded in the transaction rollback chain header block, op: Litl: xid: 0x001a. 010.00000011 uba: 0x03800085. 000e. 01flg: C --- lkc: 0 scn: 0x0000. 00853185 we should use it to replace the current 0x01 slot in 131046: Itl XidUba FlagLckScn/Fsc0x01 0x0018. 008.20.001a0x03800073.000e.02 ---- 2fsc 0x0000.000000000x02 0x0019. 007.000000200x03800053.003700001c --- 0scn 0x0000. replace 008533f6 with: Itl XidUba FlagLckScn/Fsc0x01 0x001a. 010.000000110x03800 085.000e.01C --- 0fsc 0x0000.008531850x02 0x0019. 007.000000200x03800053.003700001c --- 0scn 0x0000. 008533f6 www.2cto.com. Now, we have constructed a 131046 CR block: Itl XidUba FlagLckScn/Fsc0x01 0x001a. 010.000000110x03800085.000e.01C --- 0fsc 0x0000.008531850x02 0x0019. 007.000000200x03800053.003700001c --- 0scn 0x0000. 008533f6 Slot 0 (Row 1): 61 61 61 61 61 31 31 30 ("aaaaa10") related last commit transaction 0x1 Slot 1 (Row 2 ): 62 62 62 62 31 30 ("bbbbb10 ") Commit transaction 0x1 Slot 2 (Row 3): 63 ("c") No related transaction. Now, all transactions in itl in cr block 131046 have been committed, can we roll back till now? Not yet. Oracle also needs to compare the SCN when the query cursor is opened. If it is greater than the maximum committed SCN: 8533f6 of ITL in the preceding CR block, it means that the query occurs after the transaction ends, the approximate SCN for query is: 18> select scn, to_char (scn, 'xxxxxxxxxx') from (select dbms_flashback.get_system_change_numberSCN from dual ); SCN TO_CHAR (SCN ---------- ----------- 87109498547b9, and then submit the SCN to the maximum ITL in the CR block. Well, until now, the rollback ends. If the maximum submitted SCN is in ITL before the SCN of the cursor, Oracle will continue to roll back. Www.2cto.com for the cr block construction process, we will talk about it here. In this process, we have seldom used transaction tables. Here, ITL is important and we rely on it to roll back. However, Oracle still needs to read the transaction table at the beginning of the CR block construction. This can be seen from the "touchpoint" In X $ BH. Each time a query is released that generates a CR block, the TCH column in the rollback segment header is increased. ITL is the block header of each data block. It is for blocks, while the transaction table is for transactions. During Rollback, Oracle must read the transaction table because a transaction may involve many Rollback records and many Rollback blocks. These Rollback records are linked together, the UBA record at the end of the rollback chain can be found only in the transaction table. Therefore, rollback must start from the end of the rollback chain.