官方文档与相关视图

Administrator's Guide -> 16 Managing Undo

undo的作用

Undo data is a copy of original, premodified data. Retained at least until the transaction is ended.
Oracle使用 undo 表空间存放从datafiles读出的数据块的前镜像，供以下四种情况使用：
1）回滚事务：rollback
2）失败事务的恢复：A failed transaction occurs when a user session ends abnormally (possibly because of network errors or a failure on the client computer) before the user decides to commit or roll back the transaction. Failed transactions may also occur when the instance crashes or you issue the SHUTDOWN ABORT command.
2）读一致性：DML操作中的数据块，事务结束前，其他用户读undo里面的数据前镜像
4）闪回技术：flashback query、flashback table等

undo与redo的比较

注意：Undo block changes are also written to the redo log.

undo信息及其保存

分析DML产生的undo信息：
delete ----> 把原的所有行数据都存在undo
insert ----> 存储插入的rowid
update ---> 存储修改前的数据

undo信息保存在undo segments中，undo segments保存在undo tablespace中。Undo tablespaces：
• Are used only for undo segments
• Have special recovery considerations（必须在mount模式下恢复）
• May be associated with only a single instance
• Require that only one of them be the current writable undo tablespace for a given instance at any given time

undo的管理模式

SQL> show parameter undo_management;        ----    默认值是 AUTO

• manaual手工：使用rollback segment进行管理（回滚段，没有undo表空间一说）
  如：create rollback segment undosegment1 tablespace RBS; --- 在表空间RBS中创建回滚段
• auto自动：使用undo tablespace进行管理
  Oracle强烈建议使用auto方式来管理undo。

Automatic undo management is the default for Oracle Database 11g (and later releases). Manual undo management is supported for backward compatibility with Oracle8i and earlier releases but requires more DBA interaction. In manual undo management mode, undo space is managed through rollback segments (not through undo tablespace).

undo表空间管理

查看当前正在使用的undo表空间

SQL> show parameter _tablespace 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1    //* If not specified, Oracle uses the first one it finds

通过初始化参数undo_tablespace可以查看或者设置当前实例使用的undo表空间。一个实例同一时间只能关联到一个undo表空间。但这并不是说只能使用一个undo表空间。例如现有的undo表空间中有未提交事务的undo数据，然后将undo表空间切换为另一个undo表空间，该事务的后续undo数据将放到新的undo表空间中，此时，一致性查询将涉及到两个undo表空间。

创建undo表空间

SQL> create undo tablespace undotbs2 datafile '/u01/app/Oracle/oradata/ocp/undotbs02_01.dbf' size 100m autoextend on;

注意：Undo表空间中每个extent的大小不能uniform，只能autoallocate。即不能使用extent management local uniform，可以使用 extent management local autoallocate 或者 extent management local.

一旦创建undo表空间，不管active与否，都会在表空间中生成10个undo segment，可以使用DBA_ROLLBACK_SEGS查看。

切换undo

undo表空间创建完成后，通过初始化参数来指定实例使用的UNDO表空间。
一个UNDO表空间只能被一个实例使用，在RAC环境下，一定要注意这个问题。

下面是一个切换undo的示范：

假设现在使用的是UNDOTBS1，在一个会话中产生一个事务，但是不提交，另一个会话做如下操作：

查询当前事务及与之关联的undo信息：

SQL> select xid,xidusn from v$transaction;

XID                  XIDUSN
---------------- ----------
0A000D00D9020000         10

查看当前所有 online 的 rollback segments：

SQL> select * from v$rollname;

       USN NAME
---------- ------------------------------
         0 SYSTEM        // 用于数据字典的维护，在system表空间中
         1 _SYSSMU1_3724004606$
         2 _SYSSMU2_2996391332$
         3 _SYSSMU3_1723003836$
         4 _SYSSMU4_1254879796$
         5 _SYSSMU5_898567397$
         6 _SYSSMU6_1263032392$
         7 _SYSSMU7_2070203016$
         8 _SYSSMU8_517538920$
         9 _SYSSMU9_1650507775$
        10 _SYSSMU10_1197734989$

11 rows selected.

切换 undo 表空间：

SQL> alter system set undo_tablespace='UNDOTBS2';     //直接修改就是了，不要有顾虑

查看切换后的 undo 信息：

SQL> select * from v$rollname;

       USN NAME
---------- ------------------------------
         0 SYSTEM                // 用于数据字典的维护，在system表空间中
        10 _SYSSMU10_1197734989$    // 未提交事务存放在原undo表空间中的段中
        11 _SYSSMU11_1040830850$
        12 _SYSSMU12_3562645542$
        13 _SYSSMU13_2720166746$
        14 _SYSSMU14_556251429$
        15 _SYSSMU15_69526121$
        16 _SYSSMU16_3826427188$
        17 _SYSSMU17_3096536991$
        18 _SYSSMU18_1538192027$
        19 _SYSSMU19_2369019021$
        20 _SYSSMU20_1863331374$

12 rows selected.

SQL> select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024,shrinks from v$rollstat order by rssize;

       USN      XACTS STATUS          RSSIZE/1024/1024 HWMSIZE/1024/1024    SHRINKS
---------- ---------- --------------- ---------------- ----------------- ----------
        25          0 ONLINE                  .1171875          .1171875          0
        28          0 ONLINE                  .1171875          .1171875          0
        10          1 PENDING OFFLINE         .1171875          .1171875          0
        23          0 ONLINE                  .1171875          .1171875          0
        27          0 ONLINE                  .1796875          .1796875          0
        21          0 ONLINE                  .1796875          .1796875          0
        22          0 ONLINE                  .2421875          .2421875          0
        24          0 ONLINE                  .3046875          .3046875          0
         0          0 ONLINE                  .3671875          .3671875          0
        26          0 ONLINE                  .4921875          .4921875          0
        30          0 ONLINE                 1.1171875         1.1171875          0
        29          0 ONLINE                 1.6171875         1.6171875          0

12 rows selected.

SQL>

删除原有的UNDO表空间；

SQL> drop tablespace undotbs1including contents and datafiles;

注意：即使事务提交后，在v$rollname或者v$rollstat中关于原UNDO表空间中的UNDO段记录并不会立即消失，稍等一会儿，记录消失后，才可以删除原来的undo表空间。否则删除表空间时仍然会报错说正在使用。

将undo表空间设置为固定大小

If you decide to change the undo tablespace to a fixed size, you must choose a large enough size to avoid the following two errors:
• DML failures (because there is not enough space to the undo for new transactions)
• "Snapshot too old" errors (because there was insufficient undo data for read consistency)

将undo表空间设置为固定大小，其实就是关闭数据文件的自动扩展。

SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
SQL> alter database datafile '/u01/app/Oracle/oradata/ocp/undotbs01.dbf' autoextend off;

设置undo表空间的大小就是设置表空间中数据文件的大小或者增加该表空间中的数据文件：

SQL> alter database datafile '/u01/app/Oracle/oradata/ocp/undotbs01.dbf' resize 200m;

undo retention

什么是 undo retention

UNDO_RETENTION specifies (in seconds) how long already committed undo information is to be retained.
undo retention参数规定了已提交事务的undo数据保留多长时间，它是保证一致性读和大多数闪回技术成功的关键。

After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible. When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired and its space is available to be overwritten by new transactions. Old undo information with an age that is less than the current undo retention period is said to be unexpired and is retained for consistent read and Oracle Flashback operations.

如何设置 undo retention

用户可以 UNDO_RETENTION 参数来设置 undo retention（默认900s）。
You can optionally specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter.

undo retention 事实上是由 Oracle 自行决定的。该行为由隐含参数 _undo_autotune=TRUE 决定。
Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity.

The exact impact this parameter on undo retention is as follows:

• 对于固定表空间，直接忽略该参数
  The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size.
• 对于自动扩展表空间，Oracle尝试至少保留该参数指定的时间
  For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.

For auto extending undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to be slightly longer than the longest-running active query. 在文档“Doc ID 1951402.1”中，可以看到这样一句话……Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

Oracle自我调节后的undo retention可以通过如下方法查看：

SQL> select begin_time,end_time,undoblks,maxqueryid,maxquerylen as "MAX_QUERYTIME(s)",tuned_undoretention 
from v$undostat;


BEGIN_TIME          END_TIME              UNDOBLKS MAXQUERYID        MAX_QUERYTIME(s) TUNED_UNDORETENTION
------------------- ------------------- ---------- ----------------- ---------------- -------------------
2023-11-24 11:09:33 2023-11-24 11:19:33        897 gb6a08rq7vx1z                 2861                3581
2023-11-24 10:59:33 2023-11-24 11:09:33       3639 gb6a08rq7vx1z                 2258                2979
2023-11-24 10:49:33 2023-11-24 10:59:33       5056 gb6a08rq7vx1z                 1656                2376
2023-11-24 10:39:33 2023-11-24 10:49:33       3943 gb6a08rq7vx1z                 1053                1773

v$undostat 视图查看的是最近的调节记录，DBA_HIST_UNDOSTAT 可以查看更早历史的调节记录。
可见实际的RETENTION TIME往往远远大于设定的UNDO_RETENTION（默认900），但也不是文档“Doc ID 1951402.1”中说的(MAXQUERYLEN secs + 300)，所以不用纠结这个值是如何准确确定的，大致知道这个意思就行。

一般说来，undo一般保留两三个小时就差不多了，很少保留几天的。

Guaranteeing Undo Retention

这是一个表空间的属性，而不是一个初始化参数。只适用于undo表空间。

SQL> alter tablespace undotbs2 retention guarantee;
SQL> alter tablespace undotbs2 retention noguarantee;

查看undo表空间是否设置了guarantee属性：
SQL> select tablespace_name,retention from dba_tablespaces where contents='UNDO';

考题：All you need to do to guarantee that all queries under 15 minutes will find the undo data needed for read consistency, is set the UNDO_RETENTION parameter to 15 minutes.

1. True
2. False

答案：2

undo 数据的三种状态

SQL> select distinct status from dba_undo_extents;

STATUS
---------
UNEXPIRED
EXPIRED
ACTIVE

• active：表示transaction还没有commit，不可覆盖(永远不会被覆盖)。
• unexpired：已经commit，但是还在 undo retention 内，不可以覆盖
  非强制，尽量不覆盖，加 GUARANTEE 属性后强制 undo_retentionc 参数时间内不覆盖
• expired：已经commit，且时间超过了undo_retention，随时可以覆盖。

undo segments

关于transactions_per_rollback_segment参数

SQL> show parameter transaction;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
transactions                         integer     272
transactions_per_rollback_segment    integer     5

transactions_per_rollback_segment参数容易让人误解，在11g中，undo的管理默认使用auto管理，该参数已无效。

transactions_per_rollback_segment specifies the number of concurrent transactions you expect each rollback segment to have to handle. The minimum number of rollback segments acquired at startup is TRANSACTIONS divided by the value for this parameter. For example, if TRANSACTIONS is 272 and this parameter is 5, then the minimum number of rollback segments acquired would be the ratio 272/5, rounded up to 55. You can acquire more rollback segments by naming them in the parameter ROLLBACK_SEGMENTS.

事务与 undo 段

When a transaction starts, it is assigned to an undo segment. Throughout the life of the transaction, when data is changed, the original values (before the change) are copied into the undo segment.

Each transaction is assigned to only one undo segment. An undo segment can service more than one transaction at a time. (Administration Workshop I 10 - 5). 原则上是这样的，但是Oracle 11.2后，基本上是一个undo segment只服务一个事务，这样可以减少事务对undo segment的争用。缺省下undo tablespace会被分配10个undo segment，理论上一个段可以有多个事务，但Oracle的策略并不情愿如此，对应并发的事务，通常每个事务会分配一个UNDO段，也就是说如果这10个段不够，Oracle 会自动再增加段。

Undo segments are specialized segments that are automatically created by the instance as needed to support transactions. Undo segments automatically grow and shrink as needed. Transactions fill extents in their undo segments until a transaction is completed or all space is consumed. If an extent fills up and more space is needed, the transaction acquires that space from the next extent in the segment. After all extents have been consumed, the transaction either wraps around back into the first extent or requests a new extent to be allocated to the undo segment.

注意：Parallel DML and DDL operations can actually cause a transaction to use more than one undo segment.

system表空间的undo

默认system表空间会有一个单独的undo segment(usn为0)，其他表空间的事务不能使用它。

查看undo segment

V$ROLLNAME：lists the names of all online rollback segments.
该视图只有两列：USN和NAME。

DBA_ROLLBACK_SEGS：describes rollback segments.
该视图记录了回滚段的ID，名字，所在表空间，所在文件等信息。
SYSTEM回滚段属于SYS用户，其它回滚段属于PUBLIC。

V$ROLLSTAT：contains rollback segment statistics.

查看回滚段的统计信息：

set pages 800;
set lines 200;
SELECT n.name,s.extents,
       s.rssize/1024/1024 "SEGMENT_SIZE(M)",
       s.optsize,s.hwmsize/1024/1024 "HWMSIZE(M)",
       s.xacts,s.status,s.shrinks,s.wraps,s.extends
FROM v$rollname n,v$rollstat s
WHERE n.usn = s.usn;


NAME                              EXTENTS SEGMENT_SIZE(M)    OPTSIZE HWMSIZE(M)      XACTS STATUS             SHRINKS      WRAPS    EXTENDS
------------------------------ ---------- --------------- ---------- ---------- ---------- --------------- ---------- ---------- ----------
SYSTEM                                  6        .3671875              .3671875          0 ONLINE                   0          0          0
_SYSSMU1_766309734$                     3       1.1171875            4093.51563          0 ONLINE                1295       6636       4989
_SYSSMU2_674329671$                     4       2.1171875            87.6171875          0 ONLINE                1370       5834       4449
_SYSSMU3_300764657$                     4       2.1171875            104.117188          0 ONLINE                1298       5551       4153
_SYSSMU4_2252920807$                    4       2.1171875            63.4296875          0 ONLINE                1540       5768       4570
_SYSSMU5_1492032034$                    4       2.1171875            88.1171875          0 ONLINE                1205       5642       4140
_SYSSMU6_2786685692$                    5       3.1171875            104.117188          0 ONLINE                1369       6153       4726
_SYSSMU7_3022187959$                    5       2.1796875            120.117188          0 ONLINE                1649       6629       5408
_SYSSMU8_4048255138$                   69      80.1796875            160.242188          0 ONLINE                1553       7216       6111
_SYSSMU9_2487852131$                    5      10.1171875            315.117188          0 ONLINE                2051       9604       8664
_SYSSMU10_1376270990$                   6       4.1171875            4093.53125          0 ONLINE                4289      30166      26462
_SYSSMU21_131525862$                    4       9.1171875            3122.11719          0 ONLINE                2405      12171      10279

rssize: Size (in bytes) of the rollback segment.
optsize: Optimal size of the rollback segment.
hwmsize: High watermark of the rollback segment size.
xacts: Number of active transactions.
shrinks: Number of times the size of a rollback segment decreases.
wraps: Number of times rollback segment is wrapped.(指循环写以前的extend)
extends: Number of times rollback segment size is extended.

UNDO 空间分配规则（重用规则）

Oracle 官方文档对 undo 块的分配方法描述如下：

When transactions hit a database and they need undo space. The undo space allocation happens in the following sequence:
当一个事务初始需要 undo 空间时，undo 空间按如下顺序分配：

1. Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments.
2. If no undo segment found then oracle tries to online an off-line undo segment and use it.
3. If no undo segments to online, then we create a new undo segment and use it.
4. If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments.

不包含活动事务的 undo 段 > 尝试将 offline 的 undo 段 onlie 使用 > 创建新的 undo 段使用 > 使用现存 undo 段中的 expired extent

For a running transaction associated with undo segment/ extent, if it needs more undo space then:
如果事务已经关联了 undo 段，需要更多的 undo 空间时，undo 空间按如下顺序分配：

1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.
3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
4. If there is no free extent available then steal from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.
5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment then return the block.
7. Otherwise try to reuse unexpired extents from own undo segment. If all extents are currently busy(they contains uncommitted information) go to the step 8. Otherwise wrap into the next extent.
8. Randomly steal unexpired extents from offline undo segments. If this fails then try to online undo segments for reuse.
9. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'

当前 extent 中的 next free block > (wrap) next expired extent > 从 undo 表空间中获取 free extent > 从 offline undo 段中 steal extent > 从 online undo 段中 steal extent > 扩展 undo datafile，获得新的 extent > 尝试从自己的 undo 段中 reuse unexpired extents > 随机从 offline undo segments 中 steal unexpired extents > 尝试 reuse online undo segments > ORA-30036

UNDO表空间到底设多大

那么undo表空间到底应该设置为多大呢？

通常做法

对于自动扩展的undo表空间，通常做法创建一个最大30g的自动扩展文件，如果后面报UNDO空间不足或者快照太旧的错误，再分析决定是否扩容。

如果想对 undo 表空间做更精细化的管理，或者要把 undo 表空间设置为固定大小的 undo 表空间，则需要分析 undo 表空间到底需要多大才合适。

使用 Undo Advisor

You can access the Undo Advisor through Oracle Enterprise Manager (EM) or through the DBMS_ADVISOR PL/SQL package or through the DBMS_UNDO_ADV PL/SQL package.
The package DBMS_UNDO_ADV is undocumented , and it is used internally by the Undo Advisor .

方法一：使用EM中的advisor

"Home" page --> "Related Links" --> "Advisor Central" --> Automatic Undo Management

方法二：使用 SQL*PLUS 调用 DBMS_UNDO_ADV PL/SQL package

包 DBMS_UNDO_ADV 在 Oracle 的官方文档上是找不到的，Undo Advisor 内部调用的就是该包。
该包的详细使用可以参见官方文档 (Doc ID 1580225.1)(已保存)，下面是它可以做的事（文档均有范例）：

• Estimate the Undo Tablespace Size needed for migration from Manual To Automatic Undo management.
• Provide information about undo tablespace of the current instance .
• Determine if auto tuning of undo retention is enabled or not.
• Check the length of the longest query for a given period .
• The required undo_retention to satisfy longest query .
• Check best possible undo_retention the current undo tablespace can satisfy .
• The required undo tablespace size to satisfy certain undo retention value .
• Verify current undo_retention and undo tablespace size (check whether its optimal).
• Check if there is any problem with the current instance and provide recommendations.

下面是一个使用范例：

set serveroutput on
DECLARE utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
dbms_output.put_line('===============================================================================');
dbms_output.put_line('The Min required size of the undo TBS during all time range is : '||utbsiz_in_MB||' MB');
dbms_output.put_line('===============================================================================');
utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION(SYSDATE-1/24, SYSDATE);
dbms_output.put_line('The Min required size of the undo TBS during this time range is : '||utbsiz_in_MB||' MB');
dbms_output.put_line('===============================================================================');
END;
/

===============================================================================
The Min required size of the undo TBS during all time range is : 4082 MB
===============================================================================
The Min required size of the undo TBS during this time range is : 4082 MB
===============================================================================

PL/SQL procedure successfully completed.

当然也可以直接查询：

SQL> select DBMS_UNDO_ADV.RBU_MIGRATION(SYSDATE-1/24, SYSDATE) as "UNDO_REQUIRED(MB)" from dual;

方法三：使用 SQL*PLUS 调用 DBMS_ADVISOR PL/SQL package

使用SQL*Plus根据历史数据估计

官方文档：Doc ID 262066.1

Sizing an UNDO tablespace requires three pieces of data：
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

The undo space needed is calculated as:
UndoSpace = UR (UPS DBS)

The following query calculates the number of bytes needed to handle a peank undo activity:

SQL> SELECT (UR*(UPS*DBS))/1024/1024 AS "MB"
     FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
          (SELECT undoblks/((end_time-begin_time)*86400) AS UPS
           FROM v$undostat
           WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
          (SELECT block_size AS DBS
           FROM dba_tablespaces
           WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));

ORA-01555错误处理与模拟（snapshot too old)

在处理 ORA-01555 错误时，不要盲目的增加 undo_retention 时间或者 undo 表空间大小。
首先应该分析触发该报错的SQL语句运行时间是否正常。
在SQL语句运行没有问题的情况下，再考虑增加 undo_retention 时间或者扩容 undo 表空间。

案例一：生产中的 ORA-01555

Tue Nov 07 09:58:18 2023
ORA-01555 caused by SQL statement below (SQL ID: 6hx3fg83nb4vd, Query Duration=88966 sec, SCN: 0x0022.47189291):
INSERT INTO XXYY_LOAN_INFO SELECT FROM XXYY_LOAN_INFO A, XXYY_EXPS_INFO B WHERE

该语句运行时间长达 24 小时（Query Duration=86691 sec），去调整 undo_retention 是明显不合理的。

后面经过分析，XXYY_LOAN_INFO 的大小约为 45G，其上索引有 4 个，索引总大小约为 35G。但该表中的记录数仅有 20724 条。
很明显，该表的水位线与表中的数据量完全不匹配。后面通过重建表降低了水位线，重建后，该表大小约为 6M。

案例二：ORA-01555 故障模拟

在该示例中，只需要几秒就会产生 ORA-01555 报错。
这个时间远远小于默认的 undo_retention 900s，所以，盲目的调整 undo_retention 是没有用的。
本示例的 undo 表空间只有8M，但是盲目的增大表空间就有效果吗？如果没有找到根本原因，加再大的UNDO空间也没用。
所以，产生 ORA-01555 问题时，一定是要排查 SQL 是否有问题，其次才是考虑 undo retention 和 undo 表空间的问题。

ORA-30036（UNDO表空间不足）

ORA-30036 error is reported when the current Undo tablespace has no more free space available for the active transactions.

$ oerr ora 30036
30036, 00000, "unable to extend segment by %s in undo tablespace '%s'"
// *Cause:   the specified undo tablespace has no more space available.
// *Action:  Add more space to the undo tablespace before retrying
//           the operation. An alternative is to wait until active
//           transactions to commit.

2023.11.01 15:38:17，银联前置应用日志报错如下：
database error, sqlcode=-30036

但 Oracle alert 日志中并未产生 ORA-30036 报错，这一点需要注意一下。

ORA-30036 not logged in alert log when generated. (Doc ID 444106.1)

According to development when reviewing the code, there are a number of locations where ORA-30036 is issued because none of these locations provide any facility to write the message to the alert log.

The decision as to whether to write an error to the alert log is totally subjective and up to the code owner. ORA-30036 is very specifically coded not to be written to the alert log. This is not a bug, since internally it is decided that this error should not be alerted. If you see this error in the alert log file, then it would be with other errors.

This is discussed in the Unpublished BUG 2099510, which is closed as "Not a Bug"
Unpublished BUG 2099510 - ORA-30036 ERROR ISN'T REPORTED IN ALERT FILE AT FIRST TIME

谁用了 undo

查询 undo 的当前使用情况

查询 undo 的历史使用情况

建议周期性（如每5分钟）对 undo 的使用情况进行监控，将使用 undo 最多 sql_id 和 undo 量打印出来，如此，当需要查询过去某时间谁消耗了最多的 undo 时，就可以通过 zabbix 迅速找到。

undo 表空间的监控

col tablespace_name for a30;
col file_name for a60;
set lines 800;
set pages 800;
select total.TABLESPACE_NAME TABLESPACE_NAME,
total.MAX_MB Total_MB, total.MB-free.MB Used_MB,
round(((total.MB-free.MB)/decode(total.MAX_MB,0,total.MB,total.MAX_MB))*100,2)|| '%'  USED_PCT, total.MAX_MB-(total.MB-free.MB) Free_MB
from
    (select TABLESPACE_NAME,
sum(BYTES)/1024/1024 MB,
sum(decode(MAXBYTES, 0 , bytes,MAXBYTES ))/1024/1024 MAX_MB
 from dba_data_files 
where TABLESPACE_NAME like '%UNDO%' 
group by TABLESPACE_NAME) total, 
    (select TABLESPACE_NAME,
sum(BYTES)/1024/1024 MB
from dba_free_space
where TABLESPACE_NAME like '%UNDO%'
group by TABLESPACE_NAME) free
    where total.TABLESPACE_NAME=free.TABLESPACE_NAME
order by USED_PCT;

-- UNDO TABLE SPACE USAGE(从 oracle 官方社区找的)
SELECT a.tablespace_name,
SIZE_MB,
USAGE_MB,
(SIZE_MB - USAGE_MB) FREE_MB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZE_MB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGE_MB
FROM DBA_UNDO_EXTENTS c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

-- OUTPUT UNDO TABLE SPACE USAGE
TABLESPACE_NAME SIZE_MB         USAGE_MB        FREE_MB
UNDOTBS1    19983,9921875    19981,9375    2,0546875

查看三种状态的 undo 量：

col segment_name format a30 head "Segment Name"
col "ACT BYTES" format 999,999,999,999 head "Active Bytes"
col "UNEXP BYTES" format 999,999,999,999 head "Unexpired Bytes"
col "EXP BYTES" format 999,999,999,999 head "Expired Bytes"

select segment_name, nvl(sum(act),0) "ACT BYTES",
nvl(sum(unexp),0) "UNEXP BYTES",
nvl(sum(exp),0) "EXP BYTES"
from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp
from dba_undo_extents where status='ACTIVE' group by segment_name
union
select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp
from dba_undo_extents where status='UNEXPIRED' group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp
from dba_undo_extents where status='EXPIRED' group by segment_name)
group by segment_name
order by 1
/

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;
SELECT STATUS,TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 "SIZE(G)", COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;

Temporary Undo（12C新特性）

在Oracle Database 12c中，为了缩减UNDO段的使用，同时减少REDO和归档的数据量，一个新特性：Temporary Undo Segments被引入。

由于临时表的UNDO信息通常用于读一致性和事务回滚，在事务完成之后，无需进行恢复，所以也就不必永久保存。这个特性将对于临时表的UNDO信息分离出去，独立存储在临时表空间中，这就减少了对于UNDO段的使用。这个特性完全无损Oracle的事务一致性，但是却得到了空间缩减的好处。另外，这个特性将允许在ADG中对临时表进行DML操作。

可以在系统级或者会话级启用这个特性：
ALTER SYSTEM/SESSION SET TEMP_UNDO_ENABLED=true ；
启用之后，对于临时表的UNDO信息，将自动存储在临时表空间中。