--==================================
--Oracle 回滚(ROLLBACK)和撤销(UNDO)
--==================================
一、回滚(ROLLBACK)和撤销(UNDO)
回滚和前滚是保证Oracle数据库中的数据处于一致性状态的重要手段。
在9i版本以前
Oracle使用数据库中的回滚段来实现未提交数据或因系统故障导致实例崩溃时进行回滚操作
每一个表空间需要创建回滚段，各个表空间对回滚段实现各自的管理
在9i及后续版本
提供了一种新的回滚数据的管理方式，即使用Oracle自动管理的撤销(Undo)表空间
自动撤销管理表空间统一管理所有DML的回滚操作，简化了对于回滚工作的管理
在i,10g中的回滚段仅仅用作保留向后兼容
撤销段代替了原有版本中的回滚段，因此本文所有描述均使用撤销
撤销的实质意味着将所作的修改退回到修改前的状态，即倒退所有DML语句
二、撤销段中的内容及相关特性
对于任何DML操作而言，必须同时处理数据块和撤销块，并且还会生成重做信息
在ACID中，A、C、I要求生成撤销，D则要求生成重做
INSERT：
撤销段记录插入记录的rowid,如果需要撤销，则根据rowid将该记录删除即可
UPDATE:
撤销段记录被更新字段的原始值，撤销时将原始值覆盖新值即可
DELETE：
撤销段记录整行的数据，撤销时执行反向操作将该记录插入原表
由上可知，UNDO段中的内容总结如下：
数据为修改之前的副本
从每个改变数据的事务中获得
在事务结束前一直被保留
UNDO段中数据的作用：
用于回滚操作
读一致性和闪回查询
用于事务失败时的恢复
UNDO段与事务：
一个事物的启动，Oracle将为其分配仅仅一个UNDO段,若该段用完,则Oracle会自动为该UNDO段添加另一个区间(extent)
一个UNDO段能够同时为多个事务服务
UNDO段与UNDO表空间：
UNDO段中的内容存储在UNDO表空间
任意给定时刻只能使用一个UDNO表空间
UNDO表空间必须被创建为持久的、本地管理、可自动扩展的表空间
正在使用的UNDO表空间不能撤销或删除
UNDO表空间使用循环写的方式，与联机日志文件写相似，不同的是UNDO中可以设置了undo_retention 保留时间
UNDO段的两种管理方式：
AUTO 自动管理(推荐)
MANUAL 手动管理(仅保留)
三、与撤销相关的几个参数
--查看本机中Oracle的版本
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
--查看和UNDO相关的参数
SQL> SHOW PARAMETER undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
undo_management：
设置数据库的撤销段是否使用自动管理模式，值可以为auto或manual，当为manual时将不使用撤销段，即不使用自动管理模式
该参数为静态参数，修改后需重启实例才能生效
undo_retention：
指定撤销段数据在undo段中为非活动状态后被覆盖前保留的时间，单位为秒。在undo_management位auto时生效，为动态参数
undo_tablespace：
指定使用哪个表空间来实现数据的撤销，在undo_management位auto时生效，为动态参数
retention guarantee子句：
保证撤销保留，使用下面的操作来实现
ALTER TABLESPACE undo_tablespace_name RETENTION GUARANTEE;
--下面的查询中是当undo_management为manual时的结果集，可以看出撤销表空间的撤销段都处于offline状态
SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
--在undo_management 参数为manual时，对scott.emp插入一条新记录，收到了错误提示
--非系统表空间不能够使用回滚段
SQL> INSERT INTO scott.emp(empno,ename,salary)
2 VALUES(6666,'Jenney',3000);
INSERT INTO scott.emp(empno,ename,salary)
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
--查看段的类型，发现仅仅system表空间存在ROLLBACK 段，所以前一条插入语句收到错误提示
SQL> SELECT DISTINCT segment_type,tablespace_name FROM dba_segments
2 ORDER BY tablespace_name;
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
INDEX EXAMPLE
INDEX PARTITION EXAMPLE
LOBINDEX EXAMPLE
LOBSEGMENT EXAMPLE
NESTED TABLE EXAMPLE
TABLE EXAMPLE
TABLE PARTITION EXAMPLE
INDEX SYSAUX
INDEX PARTITION SYSAUX
LOB PARTITION SYSAUX
LOBINDEX SYSAUX
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
LOBSEGMENT SYSAUX
NESTED TABLE SYSAUX
TABLE SYSAUX
TABLE PARTITION SYSAUX
CLUSTER SYSTEM
INDEX SYSTEM
LOBINDEX SYSTEM
LOBSEGMENT SYSTEM
NESTED TABLE SYSTEM
ROLLBACK SYSTEM --与之前的版本兼容的回滚段
TABLE SYSTEM
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
TABLE TBS1
TYPE2 UNDO UNDOTBS1 --9i之后使用的撤销段
INDEX USERS
LOBINDEX USERS
LOBSEGMENT USERS
NESTED TABLE USERS
TABLE USERS
--下面将undo_management改为支持自动管理，需要重启实例
SQL> ALTER SYSTEM SET undo_management = 'auto' SCOPE = SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 67110676 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
--再次查看dba_rollback_segs视图所有的撤销段全部处于online状态
--注意第一行为system表空间的撤销段，用于系统表空间的撤销
SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE
由上面的示例可知：
ROLLBACK 段： --与之前的版本兼容的回滚段
TYPE2 UNDO 段： --9i之后使用的撤销段
关于回滚，一个时刻仅能使用一种类段类型，即要么使用与以前版本兼容的回滚段，要么使用撤销段
事实上，在i之后仅仅支持撤销段，从上面错误的提示即可证实
--查看DML语句产生的事务
SQL> SHOW USER;
USER is "SYS"
SQL> SELECT * FROM scott.emp WHERE ename = 'SCOTT';
EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO
---------- --------------- --------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3500 20
SQL> UPDATE scott.emp SET sal = sal * 2 WHERE ename = 'SCOTT';
1 row updated.
SQL> SELECT addr,xidusn,status,start_time,used_ublk
2 FROM v$transaction;
ADDR XIDUSN STATUS START_TIME USED_UBLK
-------- ---------- ---------------- -------------------- ----------
2DA2B17C 9 ACTIVE 07/10/10 20:29:08 1
--查看当前哪些用户使用撤销段以及段的大小，启动时间，活动状态等
SQL> SELECT t.xidusn,t.start_time,t.used_ublk,t.status,
s.username,r.segment_name
FROM v$transaction t
JOIN v$session s
ON t.ses_addr = s.saddr
JOIN dba_rollback_segs r
ON r.segment_id = t.xidusn ;
XIDUSN START_TIME USED_UBLK STATUS USERNAME SEGMENT_NAME
---------- -------------------- ---------- ---------------- ------------------------------ -------------
9 07/10/10 20:29:08 1 ACTIVE SYS _SYSSMU9$
四、UNDO表空间的创建与管理
创建UNDO表空间
创建语法：
CREATE UNDO TABLESPACE tablespace_name DATAFILE '...' SIZE n
更多表空间的创建：
请参照：Oracle 表空间与数据文件
切换UNDO表空间
实例中允许多个UNDO表空间存在
可以从一个UNDO表空间切换到另外一个UNDO表空间
任一时刻只能有一个UNDO表空间被指定
使用ALTER SYSTEM SET undo_tablespace = undo_tablespace_name实现切换
删除UNDO表空间
DROP TABLESPACE undo_tablespace_name
任意实例的UNDO表空间在非活动状态可以删除
对于活动状态的UNDO表空间，应当先将切换到其它表空间，在所有事务完成后再删除该表空间
演示创建、切换及删除UNDO表空间
--查看当前系统中的表空间
SQL> SELECT file_name,tablespace_name FROM dba_data_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/orcl/tbs1_2.dbf TBS1
/u01/app/oracle/oradata/orcl/tbs1_1.dbf TBS1
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
--创建一个新的UNDO表空间undo2
SQL> CREATE UNDO TABLESPACE undo2
2 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 3M
3 AUTOEXTEND ON;
Tablespace created.
SQL> SELECT file_name,tablespace_name FROM dba_data_files WHERE tablespace_name LIKE 'UNDO%';
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/undotbs02.dbf UNDO2
--查看当前系统使用的UNDO表空间为UNDOTBS1
SQL> SELECT name,value FROM v$parameter WHERE name LIKE 'undo%';
NAME VALUE
------------------------------ --------------------------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
--创建一张表tb_test用于演示，假定该会话为session1
SQL> CREATE TABLE tb_test
2 (
3 ID INT,
4 Name VARCHAR2(20)
5 );
Table created.
--插入一条记录到tb_test，此时未提交将产生UNDO 信息
SQL> INSERT INTO tb_test SELECT 1,'Robinson' FROM dual;
1 row created.
--此时打开另外一个回话，假定为session2，在session2中切换表空间
SQL> ALTER SYSTEM SET undo_tablespace = 'undo2';
System altered. --undotbs1中有未提交的事务，竟然可以成功切换？如此这般闪回时估计会有问题
SQL> SHOW PARAMETER undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undo2
--在session1中执行commit
SQL> COMMIT;
Commit complete. --成功执行了commit,且下面的查询看到了提交后的结果
SQL> SELECT * FROM tb_test;
ID NAME
---------- ------------------------------
1 Robinson
--登出系统后再次查看，结果依然存在，比较纳闷
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[uniread] Saved history (716 lines)
[oracle@robinson ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 10 21:29:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT * FROM tb_test;
ID NAME
---------- --------------------
1 Robinson
--删除UNDO表空间
--在session1中插入一条新记录
SQL> INSERT INTO tb_test SELECT 2,'Jack' FROM DUAL;
1 row created.
--在session2中将撤销表空间切换为undotbs1
SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';
System altered.
--紧接着在该回话中删除undo2，提示正在使用
SQL> DROP TABLESPACE undo2;
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO2' is currently in use
--在session1中提交事务
SQL> COMMIT;
Commit complete.
--在session2中再次删除表空间undo2，收到了相同的错误提示
SQL> /
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO2' is currently in use
SQL> SHOW PARAMETER undo --查看的确是已切换到undotbs1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs1
--在session2中再次执行表空间切换到undotbs1
SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';
System altered.
--此时undo2成功删除，可以看出需要在将活动事务提交或回滚后，再切换之后才能成功删除撤销表空间
SQL> DROP TABLESPACE undo2;
Tablespace dropped.
SQL> ho ls $ORACLE_BASE/oradata/orcl;
control01.ctl redo03.log redo2.log system01.dbf undotbs02.dbf
control02.ctl redo04.log redo3.log tbs1_1.dbf users01.dbf
example01.dbf redo07.log redo7.log tbs1_2.dbf
redo01.log redo08.log redo8.log temp01.dbf
redo02.log redo1.log sysaux01.dbf undotbs01.dbf
--删除UNDO表空间的物理文件
SQL> ho rm $ORACLE_BASE/oradata/orcl/undotbs02.dbf;
--在session1中可以看到两条记录也被成功插入
SQL> SELECT * FROM tb_test;
ID NAME
---------- --------------------
1 Robinson
2 Jack
--查看当前撤销表空间的大小
SQL> SELECT tablespace_name,bytes/1024/1024 FROM dba_data_files
2 WHERE tablespace_name = 'UNDOTBS1';
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
UNDOTBS1 30
--循环插入记录到tb_test后查看undo表空间的使用情况
SQL> BEGIN
2 FOR i IN 1..20000
3 LOOP
4 INSERT INTO tb_test VALUES(i,'Unkown Name');
5 END LOOP;
6 END;
7 /
PL/SQL procedure successfully completed.
--可以看到UNDO 表空间只用了个块
SQL> SELECT addr,xidusn,used_ublk FROM v$transaction;
ADDR XIDUSN USED_UBLK
-------- ---------- ----------
2D9FC160 6 174
SQL> SELECT 174 * 8 || 'KB' FROM dual;
174*8|
------
1392KB
五、计算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
2 FROM v$tablespace t
3 JOIN v$datafile d
4 USING (ts#)
5 WHERE t.name LIKE 'UNDO%';
NAME NAME TOTALSIZE FLA STATUS
--------------------------------- ------------------------------------------- ---------- --- -------
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 30 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
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:52:18 2010-07-12 19:02:18 47 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
关于UNDO涉及视图的更多信息，请参考oracle的在线文档
九、更多
SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
SQL 基础--> 集合运算(UNION 与UNION ALL)
扫一扫
52
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
