Functions of undo tablespace and ora-01555

Undo tablespace role and ora-01555 below we through DML operations, from a simple to a deep interpretation of the role of undo tablespace and the generation of ora-01555 errors: 1, when we issue DML statements, update t set col = 'B' where col = 'a'; how is oracle executed internally? 1. parsing in the shared pool, generate an execution plan (learn more about the sharing pool in the oracle memory structure); 2. Locate the col = 'A' data location through the execution plan, for example, this data contains the No. 54 data block in the No. 10 data file. 3. The process finds the idle undo block in the buffer cache (for details, see the buffer cache in the oracle memory structure, if no, find an available undo block in the undo tablespace and call it to the buffer cache. Assume that the undo tablespace is the No. 11 data file, this undo block is the 24 th data block. 4. Place the changed value, that is, A, into the undo block. 5, As the undo data Block changes, a redo record is generated. Assume that the redo record row number is 120; the row transaction idFile # Block # rowcolumnvalue120t11121_cola6; and The No. 54 data file is found from the buffer cache, if no data is found, it is called from the No. 10 data file. 7. Write B to the No. 54 data block. A redo record is generated because the data block has changed. The row number is 121; row number transaction idFile # Block # rowcolumnvalue121T1105410colB8. The control is returned to the user. The cursor is moved down in SQL * PLUS. 9. When the user issues the commit command, the LGWR process is triggered, write the redo logs 120 and 121 from the logbuffer to the online log file, set the transaction status mark recorded in the header of Data Block 54 and undo block 24 to submitted; 10. At this time, the No. 54 data block and the no. 24 data block are not necessarily written into the No. 10 and No. 11 data files by DBWn (exist in In this case, it is called a dirty block. Only when the dirty block reaches a certain level will DBWn be triggered to write the dirty block into the data file; 2. The role of undo: provides consistentread, rollback transaction, and Instance recovery. 1. consistent read: What is consistent read? Example: The T table contains 1000 data records. It takes 15 minutes to select * from T. Currently, it is. When user A issues the select * from T statement, user B deletes 1,000th records and submits them. How many records does user A read? If 999 rows are returned, it indicates that dirty reads occur, but the database does not allow dirty reads. Therefore, a consistent read and 1000 rows are returned, so what is the process of consistent read? First, after user A issues the SEARCH Command, the sharedpool resolution knows which data files the data to be retrieved is located in. before reading the data, process A records the scn number at. For example, the scn number before scloud: Must be smaller than or equal to scloud: 00, to retrieve data blocks, compare the scn Number of the ITL slot in the data block header. If the value is smaller than or equal to scloud: 00, it indicates that the data block has not changed and can be directly read. If the value is greater than scloud: 00 indicates that the data block has changed. Therefore, with the help of undo, we should retain the value before the change. This process is called consistency. How can we find this undo? The reason is that the ITL slot of the data block records the address and undo address in addition to the scn number. We can find the undo block based on this address to build the content of the data block before modification, such a data block is called a CR block. What if user C inserts two more records (and assume that the initrans of table T is 1, that is, an ITL slot)? When logging undo, oracle not only records the data before the change, but also records the ITL information that changes the header of the previous data block, therefore, at, the user B deleted the data block (located in N data blocks. At this time, the ITL header of N data blocks is [undo_block0/scn8: 50]). after the data block is submitted, the ITL information is undo_block1/scloud: 10. In the undo block and undo_block1, undo_block0/scn8: 50, 9: 11: C if the user performs the insert operation on N data blocks, the ITL information of N data blocks is undo_block2/scloud: 11, undo_block2 records undo_block1/scloud: 10 in addition to the information before the change. When the user searches for data at, he finds that the scn number of n data blocks is greater than scloud: 00, and reads undo_block2, however, the undo_block2 information scloud: 10 is also greater than scloud: 00, so you can find undo_block1 and find undo_ B. The information scn8: 50 in lock1 is smaller than scloud: 00 and can be read. The data block content before the modification is constructed. The undo tablespace may be overwritten after the transaction is completed, if undo_block1 was covered at, what about? Ora-01555 error. Snapshot too old, at this time the ora-01555 error after the above analysis, has been detailed; 2, rollback transaction: when the user issued the rolback command is, oracle uses the undo block information recorded in the ITL slot to find the original value and write it to the data block. 3. When the Instance recovery Instance recovers, the database is started (startup) without DBA intervention. There are two types of instance recovery: Roll Forward and roll back. Roll forward is also called cache recovery. The redo log is not an archive log. DBWR reads all the content in rodo log files to the memory for analysis and writes all the content to the data file. Roll back, also known as transaction recovery, uses undo to delete the data written in the data file that has not been submitted or cannot be rolled back.