Oracle回滚(ROLLBACK)和撤销(UNDO)(六)
ON 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
2 WHERE tablespace_name LIKE 'UNDO%';
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO GUARANTEE
--修改保留时间为分钟
SQL> ALTER SYSTEM SET undo_retention = 120;
System altered.
--循环删除tb_test中的记录，提示undo表空间空间容量不够
SQL> BEGIN
2 FOR i IN 1..1000
3 LOOP
4 DELETE FROM tb_test WHERE rownum < 1001;
5 COMMIT;
6 END LOOP;
7 END;
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
2 FROM v$undostat;
BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT
------------------- ------------------- ---------- ----------- ----------- -------------
2010-07-12 19:12:18 2010-07-12 19:22:18 6 0 0 0
2010-07-12 19:02:18 2010-07-12 19:12:18 9 0 0 0
2010-07-12 18:42:18 2010-07-12 18:52:18 2136 0 0 1
2010-07-12 18:32:18 2010-07-12 18:42:18 6 0 0 0
2010-07-12 18:22:18 2010-07-12 18:32:18 413 1541 0 0
2010-07-12 18:12:18 2010-07-12 18:22:18 179 938 0 0
2010-07-12 18:02:18 2010-07-12 18:12:18 6 0 0 0
--计算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
4 FROM 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