回滾段中保存的回滾數據有個作用一致讀和回滾回滾段是由連續block組成的區間extent組成回滾段有順序的循環的使用這些區間當當前區間寫滿的時候oracle移向下一個區間 如一個回滾段有個區間ABCD;當區間C被寫滿的時候oracle將寫區間D而當D寫滿的時候oracle將嘗試重新寫區間A這樣循環的有順序的使用區間
事務必須將回滾信息寫到回滾段中事務的當前新產生的回滾信息寫在該回滾段的位置叫做回滾段的head 而在該回滾段上最早發生的尚未提交的事務最早產生的回滾信息所在位置叫做回滾段的tail 當前區間寫滿的時候oracle移動head到下一個區間
i時每個事務只能使用一個回滾段Oracle會根據回滾段workload平均將事務分配給各個回滾段在回滾段使用上的一些規則
一個事務只能使用一個回滾段
多個事務可以共用一個區間但Active的事務不能共用一個block
回滾段的current extent寫滿的時候回滾段的Head不能夠移動到回滾段tail所在的區間
區間總是被有順序的循環的時候當head移動的時候不會跳躍區間只能移動到下一個區間
如果head不能夠使用下一個區間（如tail在下一個區間）將會分配一個新的區間extent並將新區間extent插入到這個循環使用的extent圈中這叫做回滾段的擴展
ORA snapshot too old主要是在一致讀和延遲塊清除delay block cleanout的時候產生
[參考]一致讀的步驟
Read the Data Block
Read the Row Header
Check the Lock Byte to determine whether theres an ITL entry
Read the ITL entry to determine the Transaction ID (Xid)
Read the Transaction Table using the Transaction ID If the transaction has been committed and has a System Commit Number less than the querys System Change Number update the status of the block (block cleanout) and start over at step
第步細分
IF 在Transaction Table 中根據Transaction ID 找到transaction
IF transaction 已經commit
IF query scn>commit scn
則接受該塊進行clean out返回
ELSEIF query scn
則進行一致性讀從第步向後執行
ELSEIF transaction 沒有commit
也進行一致性讀從第步向後執行
ELSEIF 在Transaction Table 中沒有找到transaction(undo header中的transaction slot被覆蓋了也說明事務已經提交因為只有提交後所在的transaction slot才能被覆蓋這樣query scn則去比較control scn在該回滾段上control scn以前的transaction都已經被提交也就是事務表中所能找到的最小的commit scn了)
IF query scn
則無法知道query scn和commit scn得大小關系出現ORA錯誤
IF query scn>control scn
則query scn肯定>commit scn
則接受該塊進行clean out並將block 中ITL標記上U表示upper bound commit 並返回
Read the last undo block (Uba)
Compare the block transaction ID with the transaction table transaction ID If the Transaction ID in the undo block doesnt equal the Transaction ID from the Transaction Table then issue ORA Snapshot Too Old 表示回滾段中回滾信息被覆蓋無法為一致讀提供必需的before image
If the Transaction IDs are identical make a copy of the data block in memory Starting with the head undo entry apply the changes to the copied data block
If the tail undo entry (the actual first undo entry in the chain or the last in the chain going backwards!) indicates another data block address read the indicated undo block into memory and repeat steps and until the undo entries dont contain a value for the data block address
When theres no previous data block address the transaction has been completely undone
If the undo entry contains:
a a pointer to a previous transaction undo block address read the Transaction ID in the previous transaction undo block header and read the appropriate Transaction Table entry Return to step
b an ITL record restore the ITL record to the data block Return to step
出現的時候首先判斷是哪個原因導致可以設置event如果因為transaction slot被覆蓋導致則增加回滾段數目如果因為回滾信息被覆蓋則增加回滾大大小錯誤比較復雜通常需要考慮很多問題
event = trace name processstate forever level
That will give you a process state dump for any process that gets an ORA error The dump will show you which block the process was trying to rollback to its snapshot SCN If its a rollback segment header block then you have your proof see more from
From:http://tw.wingwit.com/Article/program/Oracle/201311/17091.html
