當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle segment undo_71_UNDO扩展学习

發(fā)布時間：2025/3/15 编程问答 16 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle segment undo_71_UNDO扩展学习小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

UNDO擴展學習

UNDO是Oracle在UNDO SEGMENT(回滾段)中記錄的信息。下面使用UNDO這名字可能會包含兩種意思，一是前面說的回滾段中的記錄、二是UNDO整個機制。

在這部分內(nèi)容里，沒有涉及閃回技術(shù)，要知道不少閃回技術(shù)是基于UNDO機制來實現(xiàn)的，比如flashback query、flashback table、flashback version query等。

首先看下我的操作環(huán)境：

sys@L10GR204> select * from v$version;

BANNER

----------------------------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

下面列出本節(jié)要討論的內(nèi)容：

1) 什么時候聯(lián)機回滾段？

2) 如何證明SYSTEM回滾段只對系統(tǒng)對象提供服務(wù)

3) alter system set undo_tablespace = uuu;

4) 如何創(chuàng)建一個新的UNDO表空間并使用

5) 回滾段增減

6) 了解幾個參數(shù)

7) 構(gòu)造一個CR塊

8) 快速構(gòu)造ora-01555 快照過舊

9) enq: US – contention

10) Oracle INSERT、UPDATE、DELETE與Undo

什么時候聯(lián)機回滾段？

這是一個非常簡單的問題，如果大家已經(jīng)掌握了數(shù)據(jù)庫的啟動過程，那就很容易回答上了。

看下面簡單實驗：

當前實例為MOOUNT狀態(tài)

sys@L10GR204> select status from v$instance;

STATUS

------------------------

MOUNTED

這時候試圖訪問和UNDO相關(guān)視圖，將會報錯

sys@L10GR204> select * from v$rollname;

select * from v$rollname

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

當我們OPEN之后，即可讀取回滾段信息

sys@L10GR204> alter database open;

Database altered.

sys@L10GR204> col name for a55

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------------------------------- ------------------------------

0 SYSTEM ONLINE

1 _SYSSMU1$ ONLINE

2 _SYSSMU2$ ONLINE

3 _SYSSMU3$ ONLINE

4 _SYSSMU4$ ONLINE

5 _SYSSMU5$ ONLINE

6 _SYSSMU6$ ONLINE

7 _SYSSMU7$ ONLINE

8 _SYSSMU8$ ONLINE

9 _SYSSMU9$ ONLINE

10 _SYSSMU10$ ONLINE

11 rows selected.

-- 仔細看，USN為0的回滾段為SYSTEM，這是我們創(chuàng)建數(shù)據(jù)庫的時候創(chuàng)建于系統(tǒng)表空間的回滾段，只提供于更改系統(tǒng)對象做服務(wù)。

sys@L10GR204> set line 150

sys@L10GR204> col SEGMENT_NAME for a15

sys@L10GR204> col TABLESPACE_NAME for a10

sys@L10GR204> select segment_name, segment_type, tablespace_name from dba_segments where segment_name = 'SYSTEM';

SEGMENT_NAME SEGMENT_TYPE TABLESPACE

--------------- ------------------------------------ ----------

SYSTEM ROLLBACK SYSTEM

如何證明SYSTEM回滾段只對系統(tǒng)對象提供服務(wù)

為了證明這句話的正確性，我先將非SYSTEM的回滾段去除，只要將當前UNDO表空間脫機處理即可。

sys@L10GR204> select file_id, tablespace_name from dba_data_files where tablespace_name = 'UNDOTBS1';

FILE_ID TABLESPACE

---------- ----------

2 UNDOTBS1

UNDO表空間脫機需要注意操作：

在非歸檔模式如下操作

OPEN模式直接操作：

sys@L10GR204> alter database datafile 2 offline drop;

ERROR:

ORA-03114: not connected to ORACLE

alter database datafile 2 offline drop

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

告警日志頻繁輸出：

Mon Mar 25 13:35:28 2013

Errors in file /u01/app/oracle/admin/L10GR204/bdump/l10gr204_pmon_3756.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

Mon Mar 25 13:35:38 2013

Errors in file /u01/app/oracle/admin/L10GR204/bdump/l10gr204_pmon_3756.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

Mon Mar 25 13:35:38 2013

Errors in file /u01/app/oracle/admin/L10GR204/bdump/l10gr204_pmon_3756.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

Mon Mar 25 13:35:38 2013

Errors in file /u01/app/oracle/admin/L10GR204/bdump/l10gr204_pmon_3756.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

Mon Mar 25 13:35:38 2013

Errors in file /u01/app/oracle/admin/L10GR204/bdump/l10gr204_pmon_3756.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

試圖關(guān)閉數(shù)據(jù)庫

sys@L10GR204> shutdown immediate

-- 系統(tǒng)HANG住，無法正常關(guān)閉

解決方法：

sys@L10GR204> shutdown abort

sys@L10GR204> startup mount

sys@L10GR204> recover database;

sys@L10GR204> alter database open;

經(jīng)過上面一系列操作，系統(tǒng)能open了，但是，當我們再次關(guān)閉數(shù)據(jù)庫的時候，將得到錯誤提示

sys@L10GR204> shutdown immediate

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

這是因為UNDO表空間當前狀態(tài)是OFFLINE緣故

sys@L10GR204> select FILE#, status from v$datafile;

FILE# STATUS

---------- --------------

1 SYSTEM

2 OFFLINE

3 ONLINE

4 ONLINE

5 ONLINE

在線處理之后恢復(fù)正常

sys@L10GR204> alter database datafile 2 online;

Database altered.

sys@L10GR204> select FILE#, status from v$datafile;

FILE# STATUS

---------- --------------

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------------------------------- ------------------------------

0 SYSTEM ONLINE

11 _SYSSMU11$ ONLINE

在數(shù)據(jù)庫open又開啟歸檔模式下，進行如下操作：

sys@L10GR204> alter database datafile 2 offline drop;

ERROR:

ORA-03114: not connected to ORACLE

alter database datafile 2 offline drop

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

可見依然會掛起，但這時候，我們是無法通過非歸檔模式下解決方法來恢復(fù)，當打開數(shù)據(jù)庫時，直接異常終止，如下：

idle> alter database open;

alter database open

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

告警日志輸出：

Errors in file /u01/app/oracle/admin/L10GR204/bdump/l10gr204_smon_22399.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

Mon Mar 25 13:50:42 2013

Errors in file /u01/app/oracle/admin/L10GR204/bdump/l10gr204_smon_22399.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

Mon Mar 25 13:50:43 2013

Errors in file /u01/app/oracle/admin/L10GR204/udump/l10gr204_ora_23025.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/L10GR204/undotbs01.dbf'

Error 604 happened during db open, shutting down database

USER: terminating instance due to error 604

Instance terminated by USER, pid = 23025

ORA-1092 signalled during: alter database open...

這時候，我們需要手工介恢復(fù)：

idle> conn / as sysdba

idle> startup mount

[oracle@khm21 ~]$ rman target /

RMAN> recover database;

idle> alter database open;

上面是在誤操作(OPEN狀態(tài)下)之后恢復(fù)的過程，OPEN狀態(tài)下將默認回滾表空間脫機處理是較危險的操作，請務(wù)必重啟到MOUNT模式下操作

sys@L10GR204> alter database datafile 2 offline drop;

-- 不帶drop選項，會報ORA-01145

sys@L10GR204> alter database datafile 2 offline;

alter database datafile 2 offline

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

在系統(tǒng)運行于歸檔模式下的時候，則如下操作：

sys@L10GR204> shutdown immediate

sys@L10GR204> startup mount

sys@L10GR204> alter database datafile 2 offline;

sys@L10GR204> alter database open;

下面驗證system回滾段只為系統(tǒng)對象做服務(wù)，使用sys用戶操作：

sys@L10GR204> create table test(id int);

Table created.

sys@L10GR204> insert into test values(1);

1 row created.

sys@L10GR204> delete from test;

1 row deleted.

sys@L10GR204> rollback;

Rollback complete.

當前只有一個SYSTEM回滾段，我們操作對象時SYSTEM表空間里的時候，SYSTEM回滾段就為此服務(wù)，因此正常。

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------ ------------------------------

0 SYSTEM ONLINE

通過普通用戶操作：

ning@L10GR204> show user

USER is "ning"

ning@L10GR204> create table test(id int);

create table test(id int)

ERROR at line 1:

ORA-01552: cannot use system rollback segment for non-system tablespace 'ning'

-提示system回滾段無法為其他表空間提供服務(wù)。

將默認回滾表空間在線處理

sys@L10GR204> alter database datafile 2 online;

Database altered.

有了默認回滾段，操作恢復(fù)正常

ning@L10GR204> create table test(id int);

Table created.

ning@L10GR204> insert into test values(5);

1 row created.

ning@L10GR204> rollback;

Rollback complete.

這時候發(fā)現(xiàn)產(chǎn)生了一個回滾段

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------ ------------------------------

0 SYSTEM ONLINE

1 _SYSSMU1$ ONLINE

通過如下方法可以查看_SYSSMU1$回滾段的使用情況：

sys@L10GR204> select OWNER, SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, STATUS from dba_undo_extents where segment_name='_SYSSMU1$' and rownum <=3;

OWNER SEGMENT_NA TABLESPACE EXTENT_ID FILE_ID BLOCK_ID STATUS

------ ---------- ---------- ---------- ---------- ---------- ------------------

SYS _SYSSMU1$ UNDOTBS1 0 2 9 EXPIRED

SYS _SYSSMU1$ UNDOTBS1 1 2 2569 EXPIRED

SYS _SYSSMU1$ UNDOTBS1 2 2 24217 EXPIRED

alter system set undo_tablespace = uuu;

為什么要研究修改undo表空間，只是想告訴大家，當我們執(zhí)行alter system set undo_tablespace = uuu;的時候都會發(fā)生什么。

首先我們觀察當前都有哪些回滾段：

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------ ------------------------------

0 SYSTEM ONLINE

1 _SYSSMU1$ ONLINE

2 _SYSSMU2$ ONLINE

3 _SYSSMU3$ ONLINE

4 _SYSSMU4$ ONLINE

5 _SYSSMU5$ ONLINE

6 _SYSSMU6$ ONLINE

7 _SYSSMU7$ ONLINE

8 _SYSSMU8$ ONLINE

9 _SYSSMU9$ ONLINE

10 _SYSSMU10$ ONLINE

11 rows selected.

創(chuàng)建一個新的undo表空間，注意，這里為下面的實驗，我故意創(chuàng)建了非常小的回滾表空間。

sys@L10GR204> create undo tablespace undo_ning datafile '/u01/app/oracle/oradata/L10GR204/undo_ning.dbf' size 150K autoextend off;

Tablespace created.

將當前默認undo tablespace設(shè)置成新建的

sys@L10GR204> alter system set undo_tablespace=undo_ning;

System altered.

發(fā)現(xiàn)原先的回滾段全部消失，表空間過小，一個回滾段都沒創(chuàng)建。

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------ ------------------------------

0 SYSTEM ONLINE

添加數(shù)據(jù)文件

sys@L10GR204> alter tablespace undo_ning add datafile '/u01/app/oracle/oradata/L10GR204/undo_ning02.dbf' size 200k autoextend on;

Tablespace altered.

生成一個回滾段。

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------ ------------------------------

0 SYSTEM ONLINE

12 _SYSSMU12$ ONLINE

結(jié)論：在我們在線修改undo tablespace的時候，并不是原先的回滾段移動到新的undo tablespace，而是脫機重新創(chuàng)建。但如果原undo tablespace里還有active事務(wù)，則需要等到這些活動事務(wù)全部結(jié)束之后才能切換。

如何創(chuàng)建一個新的UNDO表空間并使用

上面已經(jīng)演示過創(chuàng)建新UNDO表空間并使用的過程，這里分享一下如果創(chuàng)建過程遇到ORA-30015如何去解決。

sys@L10GR204> create undo tablespace l_u_s datafile '/u01/app/oracle/oradata/L10GR204/l_u_s01.dbf' size 1M autoextend off;

Tablespace created.

當我想使用新創(chuàng)建的undo表空間的時候，得到錯誤提示

sys@L10GR204> alter system set undo_tablespace=l_u_s;

alter system set undo_tablespace=l_u_s

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-30015: previously offlined undo tablespace 'UNDO_ning' is still pending

查一下當前回滾段狀態(tài)，發(fā)現(xiàn)_SYSSMU19$狀態(tài)為PENDING OFFLINE，估計還有個活躍事務(wù)。

sys@L10GR204> col NAME for a20

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- -------------------- ------------------------------

0 SYSTEM ONLINE

19 _SYSSMU19$ PENDING OFFLINE

34 _SYSSMU34$ ONLINE

35 _SYSSMU35$ ONLINE

36 _SYSSMU36$ ONLINE

37 _SYSSMU37$ ONLINE

38 _SYSSMU38$ ONLINE

39 _SYSSMU39$ ONLINE

40 _SYSSMU40$ ONLINE

9 rows selected.

我們通過下面腳本區(qū)找當前活躍事務(wù)SESSION信息

sys@L10GR204> col USERNAME for a10

sys@L10GR204> col ROLLBACK for a15

sys@L10GR204> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"

2 FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R

3 WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.USN

4 AND R.name IN (SELECT segment_name FROM dba_rollback_segs WHERE TABLESPACE_NAME ='UNDO_ning')

5 /

SID SERIAL# USERNAME ROLLBACK

---------- ---------- ---------- ---------------

148 34 ning _SYSSMU19$

直接將該SESSION KILL掉

sys@L10GR204> alter system kill session '148,34';

System altered.

這下就可以正常設(shè)置新UNDO表空間了

sys@L10GR204> alter system set undo_tablespace='L_U_S';

System altered.

sys@L10GR204> alter system set undo_retention=1;

System altered.

回滾段增減

在上面，我們已經(jīng)看出，當Oracle數(shù)據(jù)庫啟動的時候，默認就會創(chuàng)建多個回滾段，但是，某些系統(tǒng)這些回滾段是不夠用的，表現(xiàn)在事務(wù)量非常大的系統(tǒng)中。

不過不用擔心，Oracle也知道有這種現(xiàn)象，因此如果當前回滾段不足，則會自動創(chuàng)建新回滾段并提供使用。一段時間(通過指定算法)后如果事務(wù)量減少，Oracle的SMON將部分回滾段脫機。

我們在上面環(huán)境的基礎(chǔ)下，通過實驗觀察這個效果。

這里用到了如下腳本

[oracle@khm21 ~]$ cat showsysevent.sql

column EVENT for a65

select *

from (

select event, total_waits, time_waited

from v$system_event

where wait_class <> 'Idle'

order by 3 desc

)

where EVENT = 'enq: US - contention'

查看當前回滾段情況：

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------ ------------------------------

0 SYSTEM ONLINE

12 _SYSSMU12$ ONLINE

13 _SYSSMU13$ ONLINE

創(chuàng)建一張測試對象表并插入數(shù)據(jù)

ning@L10GR204> create table khm(id number, domain varchar2(20));

Table created.

ning@L10GR204> begin

2 for i in 1 .. 100 loop

3 insert into khm values(i,'Integrated Cloud Applications and Platform Services');

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

創(chuàng)建一個過程，對khm表進行delete，然后馬上回滾：

create or replace procedure khm_delete

begin

for i in 1 .. 100 loop

delete from khm;

rollback;

end loop;

end;

打開100個會話并執(zhí)行該存儲過程

var jobno number;

begin

for l in 1 .. 100 loop

dbms_job.submit(:jobno, 'khm_delete;');

commit;

end loop;

end;

發(fā)現(xiàn)回滾段自動增加了幾個

sys@L10GR204> select rn.usn, rn.name, rs.status from v$rollname rn, v$rollstat rs where rn.usn = rs.usn;

USN NAME STATUS

---------- ------------------------------ ------------------------------

0 SYSTEM ONLINE

12 _SYSSMU12$ ONLINE

13 _SYSSMU13$ ONLINE

14 _SYSSMU14$ ONLINE

15 _SYSSMU15$ ONLINE

16 _SYSSMU16$ ONLINE

6 rows selected.

下面是US鎖等待統(tǒng)計，具體下面討論

ning@L10GR204> @showsysevent

EVENT TOTAL_WAITS TIME_WAITED

----------------------------------------------------------------- ----------- -----------

enq: US - contention 26 339

了解幾個參數(shù)

我不會去解釋所有和UNDO相關(guān)的參數(shù)，UNDO_MANAGEMENT、UNDO_RETENTION、UNDO_TABLESPACE這三個參數(shù)我也不解釋。

使用下面腳本查看和回滾相關(guān)的一些參數(shù)，了解一下即可，除非特殊情況不建議修改（尤其是隱含參數(shù)）

sys@L10GR204> set pagesize 9999

sys@L10GR204> set line 150

sys@L10GR204> col NAME for a30

sys@L10GR204> col VALUE for a20

sys@L10GR204> col DESCRIB for a80

sys@L10GR204> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIB

2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

3 WHERE x.inst_id = USERENV ('Instance')

4 AND y.inst_id = USERENV ('Instance')

5 AND x.indx = y.indx

6 AND x.ksppinm LIKE '%&par%'

sys@L10GR204> /

Enter value for par: undo

old 6: AND x.ksppinm LIKE '%&par%'

new 6: AND x.ksppinm LIKE '%undo%'

NAME VALUE DESCRIB

------------------------------ -------------------- --------------------------------------------------------------------------------

_gc_undo_affinity TRUE if TRUE, enable dynamic undo affinity

_kcl_undo_locks 128 number of locks per undo segment

_kcl_undo_grouping 32 grouping for undo block locks

_gc_undo_affinity_locks TRUE if TRUE, get affinity locks for undo

_gc_dissolve_undo_affinity FALSE if TRUE, dissolve undo affinity after an offline

_gc_initiate_undo_affinity TRUE if TRUE, initiate undo affinity after an online

undo_management AUTO instance runs in SMU mode if TRUE, else in RBU mode

undo_tablespace UNDO_ning use/switch undo tablespace

_collect_undo_stats TRUE Collect Statistics v$undostat

_undo_debug_mode 0 debug flag for undo related operations

_verify_undo_quota FALSE TRUE - verify consistency of undo quota statistics

_in_memory_undo TRUE Make in memory undo for top level transactions

_undo_autotune TRUE enable auto tuning of undo_retention

undo_retention 900 undo retention in seconds

_smon_undo_seg_rescan_limit 10 limit of SMON continous undo segments re-scan

_undo_debug_usage 0 invoke undo usage functions for testing

_optimizer_undo_cost_change 10.2.0.4 optimizer undo cost change

_optimizer_undo_changes FALSE undo changes to query optimizer

18 rows selected.

sys@L10GR204> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIB

2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

3 WHERE x.inst_id = USERENV ('Instance')

4 AND y.inst_id = USERENV ('Instance')

5 AND x.indx = y.indx

6 AND x.ksppinm LIKE '%&par%'

sys@L10GR204> /

Enter value for par: rollback

old 6: AND x.ksppinm LIKE '%&par%'

new 6: AND x.ksppinm LIKE '%rollback%'

NAME VALUE DESCRIB

------------------------------ -------------------- --------------------------------------------------------------------------------

transactions_per_rollback_segm 5 number of active transactions per rollback segment

ent

rollback_segments undo segment list

_rollback_segment_initial 1 starting undo segment number

_rollback_segment_count 0 number of undo segments

_offline_rollback_segments offline undo segment list

_corrupted_rollback_segments corrupted undo segment list

_cleanup_rollback_entries 100 no. of undo entries to apply per transaction cleanup

_rollback_stopat 0 stop at -position to step rollback

fast_start_parallel_rollback LOW max number of parallel recovery slaves that may be used

9 rows selected.

從中介紹幾個隱含參數(shù)：

_undo_autotune ：從Oracle 10g R2開始默認為TRUE，意思是根據(jù)undo表空間大小或者系統(tǒng)負載情況，自動調(diào)整undo_retention。這個功能可以在線關(guān)閉，方法：alter system set "_undo_autotune"=false;

_smon_undo_seg_rescan_limit：limit of SMON continous undo segments re-scan，上面介紹了Oracle脫機回滾段是根據(jù)算法的，算法里面的其中一個條件（如果前面條件都不滿足則采用此條件）就是參考_smon_undo_seg_rescan_limit的值。

_rollback_segment_count：指定在線狀態(tài)的回滾段個數(shù)。

構(gòu)造一個CR塊

下面是簡單構(gòu)造一個CR塊的示例。

SESSION A:

ning@L10GR204> select distinct sid from v$mystat;

SID

----------

149

當前KHM表id=95的記錄如下：

ning@L10GR204> select * from khm where id = 95;

ID DOMAIN

---------- ----------------------------------------

95 Integrated Cloud Applications and Platform Services

打開一個游標，但不執(zhí)行

ning@L10GR204> variable l refcursor

ning@L10GR204> exec open :l for select * from khm where id = 95;

PL/SQL procedure successfully completed.

SESSION B:

ning@L10GR204> select distinct sid from v$mystat;

SID

----------

143

在另一個會話將id=95的記錄值修改并提交

ning@L10GR204> update khm set domain = 'Create your own social network with the best community website builder - NING' where id = 95;

1 row updated.

ning@L10GR204> commit;

Commit complete.

回到打開游標的會話，執(zhí)行并讀取，這時我們發(fā)現(xiàn)讀取到的是被SESSION B修改之前的記錄，這就從CR塊里讀出來的記錄

SESSION A:

ning@L10GR204> print l

ID DOMAIN

---------- ----------------------------------------

95 Integrated Cloud Applications and Platform Services

再次查詢，因為事務(wù)已經(jīng)提交，所以讀取到的是UPDATE之后的內(nèi)容

ning@L10GR204> select * from khm where id = 95;

ID DOMAIN

---------- ----------------------------------------

95 Create your own social network with the best community website builder - NING

這其中SCN的作用是非常重要的，其實SESSION A打開游標的時候就有對應(yīng)的SCN，這個SCN肯定小于更新并提交時SCN，當我們print的時候還是拿當前SCN去查詢，經(jīng)過跟塊上的SCN比較之后它去回滾段讀取鏡像數(shù)據(jù)。

下面我們深入了解一下構(gòu)造CR塊并讀取過程。

拿下面數(shù)據(jù)作為參考進行實驗：

ning@L10GR204> select rowid, t.* from khm t where t.id >95;

ROWID ID DOMAIN

------------------ ---------- ----------------------------------------

AAANIuAAFAAAAAWABf 96 Create your own social network with the best community website builder - NING

AAANIuAAFAAAAAWABg 97 Create your own social network with the best community website builder - NING

AAANIuAAFAAAAAWABh 98 Create your own social network with the best community website builder - NING

AAANIuAAFAAAAAWABi 99 Integrated Cloud Applications and Platform Services

AAANIuAAFAAAAAWABj 100 Integrated Cloud Applications and Platform Services

SESSION A更新兩條記錄

ning@L10GR204> update khm set domain='bbs.ning.com' where id=96;

1 row updated.

ning@L10GR204> update khm set domain='ffdg' where id=97;

1 row updated.

查看該事務(wù)存在信息

ning@L10GR204> @showtra

Enter value for sid: 149

old 13: WHERE SID=&SID

new 13: WHERE SID=149

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------

50 12 3 13 44 3 45 ACTIVE

通過USN號去查看當前使用的回滾段

sys@L10GR204> select USN, NAME from v$rollname where USN=50;

USN NAME

---------- -------------------------

50 _SYSSMU50$

如果想dump該回滾段頭信息，則如下方式操作：

sys@L10GR204> alter system dump undo header '_SYSSMU50$';

sys@L10GR204> oradebug setmypid

sys@L10GR204> oradebug tracefile_name

/u01/app/oracle/admin/L10GR204/udump/l10gr204_ora_4085.trc

如果想dump該回滾段內(nèi)容看，則如下方式操作：

sys@L10GR204> alter system dump datafile 13 block 44;

sys@L10GR204> oradebug setmypid

sys@L10GR204> oradebug tracefile_name

/u01/app/oracle/admin/L10GR204/udump/l10gr204_ora_4277.trc

至于DUMP出來的內(nèi)容如何去看，我將在下面分析，這里不做解釋。

下面是簡單演示如何通過v$bh查看cr狀態(tài)xcur狀態(tài)：

SESSION A:

當前是free狀態(tài)

sys@L10GR204> select file#, block#, status from v$bh where file#=5 and block#=23;

FILE# BLOCK# STATUS

---------- ---------- --------------

5 23 free

插入一條記錄并查看

ning@L10GR204> insert into khm values(1,'恩墨學院 - 名師傳道，授業(yè)以專');

1 row created.

ning@L10GR204> @showrowid

Enter value for table_name: khm

old 7: from &table_name

new 7: from khm

Enter value for id: 1

old 8: where id=&id

new 8: where id=1

ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)

------------------ ---------- ---------- ---------- ---------- ------------------------------------

AAANIuAAFAAAAAXAAA 53806 5 23 0 AAANIuAAFAAAAAXAAA

sys@L10GR204> select file#, block#, status from v$bh where file#=5 and block#=23;

FILE# BLOCK# STATUS

---------- ---------- --------------

5 23 free

5 23 xcur

5 23 cr

-- INSERT一條記錄產(chǎn)生了一個XCUR塊兒以及CR塊兒。

SESSION B:

查詢該表：

ning@L10GR204> select * from khm;

ID DOMAIN

---------- ----------------------------------------

91 Create your own social network with the best community website builder - NING

92 Create your own social network with the best community website builder - NING

93 Create your own social network with the best community website builder - NING

94 Create your own social network with the best community website builder - NING

95 Create your own social network with the best community website builder - NING

96 Create your own social network with the best community website builder - NING

97 Create your own social network with the best community website builder - NING

98 Create your own social network with the best community website builder - NING

99 Integrated Cloud Applications and Platform Services

100 Integrated Cloud Applications and Platform Services

10 rows selected.

結(jié)果又產(chǎn)生了一個CR塊兒

sys@L10GR204> select file#, block#, status from v$bh where file#=5 and block#=23;

FILE# BLOCK# STATUS

---------- ---------- --------------

5 23 free

5 23 xcur

5 23 cr

SESSION C: