oracle 撤销回退,Oracle 回滚(ROLLBACK)和撤销(UNDO)

五、计算UNDO表空间的大小

计算公式：

MAX(undoblks)/600 * MAX(maxquerylen)位于v$undostat

* db_block_size位于v$parameter

--创建演示环境

SQL> INSERT INTO tb_test SELECT employee_id,first_name FROM hr.employees;

107 rows created

SQL> INSERT INTO tb_test SELECT * from tb_test;

109 rows created.

--多次执行上述命令，下面是的tb_test表中的记录数

SQL> /

892928 rows created.

SQL> COMMIT;

Commit complete.

--查看当前undo表空间的大小

SQL> SELECT t.name,d.name,d.bytes/1024/1024 as TotalSize ,t.flashback_on,d.status

2FROM v$tablespace t

3JOIN v$datafile d

4USING (ts#)

5WHERE t.name LIKE 'UNDO%';

NAMENAMETOTALSIZE FLA STATUS

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

UNDOTBS1/u01/app/Oracle/oradata/orcl/undotbs01.dbf30 YES ONLINE

--将undo表空间修改为RETENTION GUARANTEE及关闭自动扩展

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Tablespace altered.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf' AUTOEXTEND OFF;

Database altered.

SQL> SELECT tablespace_name,contents,retention FROM dba_tablespaces

2WHERE tablespace_name LIKE 'UNDO%';

TABLESPACE_NAMECONTENTSRETENTION

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

UNDOTBS1UNDOGUARANTEE

--修改保留时间为分钟

SQL> ALTER SYSTEM SET undo_retention = 120;

System altered.

--循环删除tb_test中的记录，提示undo表空间空间容量不够

SQL> BEGIN

2FOR i IN 1..1000

3LOOP

4DELETE FROM tb_test WHERE rownum < 1001;

5COMMIT;

6END LOOP;

7END;

8/

BEGIN

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

ORA-06512: at line 4

--修改回话的时间参数

SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:MI:SS';

Session altered.

--查看v$undostat视图，获得相关信息

SQL> SELECT begin_time,end_time,undoblks,maxquerylen, ssolderrcnt,nospaceerrcnt

2FROM v$undostat;

BEGIN_TIMEEND_TIMEUNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT

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

2010-07-12 19:12:18 2010-07-12 19:22:186000

2010-07-12 19:02:18 2010-07-12 19:12:189000

2010-07-12 18:52:18 2010-07-12 19:02:1847000

2010-07-12 18:42:18 2010-07-12 18:52:182136001

2010-07-12 18:32:18 2010-07-12 18:42:186000

2010-07-12 18:22:18 2010-07-12 18:32:18413154100

2010-07-12 18:12:18 2010-07-12 18:22:1817993800

2010-07-12 18:02:18 2010-07-12 18:12:186000

--计算undo表空间所需的大小

SQL> SELECT (

2(SELECT MAX(undoblks)/600 * MAX(maxquerylen) FROM v$undostat) *

3(SELECT value FROM v$parameter WHERE name = 'db_block_size'))/1024/1024 as Need_Size

4FROM dual;

NEED_SIZE

----------

42.8590625

--取消撤销保留选项

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

Tablespace altered

六、UNDO配额

对于超长的事务或不当的SQL脚本将耗用大量的UNDO表空间，使用UNDO表空间配额可以提高资源的利用率

对于不同组的用户可以分配不同的最大UNDO表空间配额

当某个组超出了最大的资源限制，则该组不允许新的事务产生，直到当前组的UNDO表空间释放或终止

七、撤销常见的两个错误

1.ORA-1555 snapshot too old快照过旧错误的解决

配置合适的保留时间(undo_retention)

调整undo表空间的大小

考虑保证撤销保留的使用(retention guarantee)

2.ORA-30036 unable to extend segment in undo tablespace无法扩展撤销表空间内的撤销段

调整undo表空间的大小

确保大量的事务能够周期性的提交

八、UNDO涉及的几个相关视图：

V$TRANSACTION

V$SESSION

DBA_ROLLBACK_SEGS--显示所有的segments

V$ROLLSTAT

V$UNDOSTAT

V$ROLLNAME--显示当前在线的segments

关于UNDO涉及视图的更多信息，请参考oracle的在线文档