理解UNDO表空间-CSDN博客

系统可以有多个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;