理解UNDO表空間

系統可以有多個UNDO表空間，但一個時間點只能一個是Active的。只有在這種情況下會有兩上UNDO Active。當原來用一個UNDO，又重新定義一個UNDO，這樣還沒完成的事務用原來舊的UNDO，後面開始的新事務用新UNDO。[@more@]

一個segment 至少要有2個extent,對於8K的block，最多可以有32,765個extent.

一個事務只能在一個undo segment，當用完一個extent時，會使用下一個extent,當所有extent用完時，系統會分配更多的extent.但一個undo segment可以讓多個事務同時用。

當一個事務已經在查詢，另一個DML語句產生， UNDO裡的資料用來提供一致性查詢。

插入語句用很少的UNDO，他只存插入的新行的指標到UNDO，當回滾時，這個指標找到這些行並執行刪除操作。

The data dictionary view DBA_ROLLBACK_SEGS shows both active (online) andinactive (offline) undo segments in both the SYSTEM and undo tablespaces

SQL> select * from v$rollname;

USN NAME

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

0 SYSTEM

1 _SYSSMU1$

2 _SYSSMU2$

3 _SYSSMU3$

4 _SYSSMU4$

5 _SYSSMU5$

6 _SYSSMU6$

7 _SYSSMU7$

8 _SYSSMU8$

9 _SYSSMU9$

10 _SYSSMU10$

11 rows selected.

The undo segment with an undo segment number (USN) of 0 is an undo segment reserved for exclusive use by system users such as SYS or SYSTEM or if no other undo segments are online and the data being changed resides in the SYSTEM tablespace. In this example, nine other undo segments

are available in the undo tablespace for user transactions.

The dynamic performance view V$TRANSACTION shows the relationship between a transaction and the undo segments. In the following query, you begin a transaction and then join V$TRANSACTION to V$ROLLNAME to find out the name of the undo segment assigned to the transaction:

SQL> set transaction name 'Update clerk salaries';

Transaction set.

SQL> update hr.employees set salary = salary * 1.25

2 where job_id like '%CLERK';

44 rows updated.

SQL> select xid, status, start_time, xidusn seg_num,

2 r.name seg_name

3 from v$transaction t join v$rollname r

4 on t.xidusn = r.usn

5 where t.name = 'Update clerk salaries';

XID STATUS START_TIME SEG_NUM SEG_NAME

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

02002F00 ACTIVE 08/01/04 16:20:10 2 _SYSSMU2$

9A140000

1 row selected.

為保證一致性，也可以用這個語句：

SQL> set transaction read only;

Transaction set.

這樣只顯示這個事務開始之前提交過的事務結果。Only the following statements are permitted in a read-only transaction:

_ SELECT statements without the FOR UPDATE clause

_ LOCK TABLE

_ SET ROLE

_ ALTER SESSION

_ ALTER SYSTEM

UNDO的兩個錯：

Running

1、out of undo space for transactions generates messages such as

ORA-01650: Unable to extend rollback segment;

2、long-running queries whose undo entries have been reused by current transactions typically receive the ORA-01555: Snapshot too old message.

Undo_rention

The parameter UNDO_RETENTION specifies, in seconds, how long undo information that has already been committed should be retained until it can be overwritten. This is not a guaranteed limit: if the number of seconds specified by UNDO_RETENTION has not been reached, and if a

transaction needs undo space, already committed undo information can be overwritten.

當把UNDO_RENTION設定為0時，開啟undo retention 自動最佳化，為了保證最長事務的UNDO，系統自動最大限制地不去增加EXTENT。在任何情況下，系統最少保持UNDO資訊900S。對未提交的事務不會覆蓋UNDO資訊。

當你認為UNDO已經設定為一個合理值時，就關掉自動EXTENT。這樣單個使用者才不會佔用太多UNDO而不去經常提交事務。

檢視V$UNDOSTAT來設定UNDO大小

SQL> select

2 to_char(begin_time,'yyyy-mm-dd hh24:mi:ss')

3 starttime,

4 to_char(end_time,'yyyy-mm-dd hh24:mi:ss')

5 endtime,

6 undoblks,

7 maxquerylen maxqrylen

8 from v$undostat;

STARTTIME ENDTIME UNDOBLKS MAXQRYLEN

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

2004-08-01 08:46:11 2004-08-01 08:48:47 13 0

2004-08-01 08:36:11 2004-08-01 08:46:11 61 0

2004-08-01 08:26:11 2004-08-01 08:36:11 31 0

retention guarantee不是在引數檔案裡設定的，只能用語句對UNDO表空間進行設定。

SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

Turning off the parameter is just as easy, as you can see in the next example:

SQL> alter tablespace undotbs1 retention noguarantee;

Tablespace altered.