Oracle 回滚(ROLLBACK)和撤销(UNDO)

落日映苍穹つ 2022-03-19 13:38 290阅读 0赞

原文： http://blog.csdn.net/robinson\_0612/article/details/5731158

\--==================================

\--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 表空间与数据文件][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

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的在线文档

九、更多

[Oracle 参数文件][Oracle 1]

[SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)][SQL_--_START BY ... CONNECT BY PRIOR]

[Oracle 用户、对象权限、系统权限][Oracle 2]

[Oracle 角色、配置文件][Oracle 3]

[SQL 基础--> 集合运算(UNION 与UNION ALL)][SQL_--_UNION_UNION ALL]

[Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/06/23/5688875.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/06/23/5688997.aspx
[SQL_--_UNION_UNION ALL]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611716.aspx