Oracle-UNDO篇
原文地址：https://www.modb.pro/db/70802?xzs=
一：请描述什么是Oracle Undo。
二：请描述UNDO的作用。
三：请谈谈你对Manual Undo Management和Automatic Undo Management管理的理解。
四：请描述UNDO Retention。
五：如何确保在undo_retention期间的数据不会被覆盖。
六：请描述ORA-01555错误原因和解决思路。
七：请描述LOB字段产生ORA-01555错误原因和解决思路。
八：请描述ORA-30036错误原因和解决思路。
九：当UNDO表空间不足时，如何将相关信息记录在告警日志中。
十：如何评估所需UNDO大小。
十一：请描述处理过UNDO相关的ORA-600错误。
十二：如何限制指定用户使用UNDO大小。
十三：如果设置Temporary Undo。
十四：DML(insert/delete/update)哪种操作产生的UNDO最多。
十五：请介绍UNDO常用的数据字典和视图有哪些。
十六：请介绍UNDO常用查询语句。
一:请描述什么是Oracle Undo？
官方解释如下：
什么是UNDO？
What Is Undo?
Oracle数据库创建和管理用于回滚或撤消对数据库的更改的数据。
Oracle Database creates and manages information that is used to roll back, or undo, changes to the database.
这些信息主要包括交易行为的记录，主要是在交易被提交之前。
Such information consists of records of the actions of transactions, primarily before they are committed.
这些记录统称为undo。
These records are collectively referred to as undo.
二：请描述UNDO的作用?
1.回滚事务。
2.实例恢复。
实例恢复过程中，想通过redo记录对checkpoint之后的脏块队列进行前滚操作。
对于所有未提交的脏块，oracle根据undo的前镜像进行回滚（行级别的逻辑反操作），重新将内存中缓存的相关数据脏块换为非脏块。
3.提供一致性读。
执行查询时，服务器进程扫描这个表中的数据块时，会把每个数据块ITL槽中最大的SCN与查询SCN进行比较，如果比查询SCN小则说明这个数据块没有被修改服务器进程直接进行数据读取即可。
如果数据块ITL槽中的SCN大于查询SCN那么说明这个数据块在发起查询后被修改了，需要借助undo去获取发起查询那个时刻数据块的数据。
4.闪回部分相关操作
闪回查询
例如：
select * from t1 as of timestamp (systimestamp - interval '120' second); ---查询t1表120秒之前的数据
select * from t1 as of timestamp (systimestamp - interval '2' minute); ---查询t1表2分钟之前的数据
select * from t1 as of timestamp (systimestamp - interval '12' hour); ---查询t1表12小时之前的数据
select * from t1 as of timestamp (systimestamp - interval '12' day); ---查询t1表12天之前的数据
select * from t1 as of timestamp to_timestamp('2021-05-23 19:30:30','yyyy-mm-dd hh24:mi:ss'); ---查询之前指定时间点数据
select * from t1 as of timestamp sysdate-2; ---查询t1表2天之前的数据
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
SELECT * FROM FLASH_TBL AS OF SCN 1257245; ---查询指定scn时刻数据
select timestamp_to_scn(to_timestamp('2014-08-24 05:15:22','yyyy-mm-dd hh24:mi:ss')) scn from dual; --时间和scn之间的转换
闪回版本查询
select versions_starttime,
versions_endtime,
versions_xid,
versions_operation,
rate
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME;
注：versions_starttime : 这个数据开始生效的时间
VERSIONS_ENDTIME ：这个数据失效的时间--一般就是下面一条记录开始的时间
versions_xid : 显示了更改该行的事务标识符。
VERSION_OPERATION ：这条记录执行的操作(Insert/Update/Delete)
SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000D00000029';
闪回事务查询。
闪回事务查询有别于闪回查询的特点有以下3个：
（1）其正常工作不但需要利用撤销数据，还需要事先启用最小补充日志。
（2）返回的结果不是以前的“旧”数据，而是能够将当前数据修改为以前的样子的撤销SQL（Undo SQL）语句。
（3）集中地在名为flashback_transaction_query表上查询，而不是在各个表上通过“as of”或“versions between”子句查询。
SQL> alter database add supplemental log data;
例如：
select versions_xid, versions_startscn, department_id, department_name
from hr.departments versions between timestamp minvalue and maxvalue
where department_id = 999
order by 2 nulls first;
官方解释如下：
Undo记录用于：
Undo records are used to:
1 当执行rollback命令时，回滚事务。
Roll back transactions when a ROLLBACK statement is issued
2 恢复数据库。
Recover the database
3 提供一致性读。
Provide read consistency
4 使用Oracle Flashback查询分析截至较早时间点的数据
Analyze data as of an earlier point in time by using Oracle Flashback Query
5 使用Oracle Flashback功能从逻辑损坏中恢复
Recover from logical corruptions using Oracle Flashback features
When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction.
During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the data files.
Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.
三：请谈谈你对Manual Undo Management和Automatic Undo Management管理的理解？
Manual Undo Management
手工管理重做段的创建、分配、调整。Oracle9i之前的唯一方法。
回滚段的分配和使用
当有事务产生时，数据库会给事务分配一个回滚段。当然我们可以指定事务使用某个回滚段。
select SEGMENT_ID ,SEGMENT_NAME from dba_rollback_segs;
set transaction use rollback segment rbsxxx;
如果我们不人为的指定使用哪个回滚段，则数据库会根据回滚段中事务来权衡，以使得所有回滚段中事务压力尽可能平均。
在这种模式下，通过回滚段管理撤消空间，不使用UNDO表空间。
The database can also run in manual undo management mode.
In this mode, undo space is managed through rollback segments, and no undo tablespace is used.
注：
回滚段的空间管理很复杂。Oracle强烈建议将数据库置于自动撤消管理模式。
Note:
Space management for rollback segments is complex. Oracle strongly recommends leaving the database in automatic undo management mode.
Automatic Undo Management
请描述自动撤销管理Automatic Undo Management
Introduction to Automatic Undo Management
Oracle提供了一种完全自动化的机制，称为自动撤消管理，用于管理撤消信息和空间。
Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space.
通过自动撤消管理，数据库管理撤消表空间中的撤消段。
With automatic undo management, the database manages undo segments in an undo tablespace.
当数据库实例启动时，数据库会自动选择第一个可用的undo表空间。
When the database instance starts, the database automatically selects the first available undo tablespace.
如果没有可用的undo表空间，则实例将在没有undo表空间的情况下启动，并在SYSTEM系统表空间中存储undo记录。
If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.
不建议这样做，并且会向警报日志文件中写入一条警报消息，以警告系统在没有撤消表空间的情况下运行。
This is not recommended, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace.
如果数据库包含多个undo表空间，则可以选择在启动时指定要使用特定undo表空间。这是通过设置UNDO_TABLESPACE初始化参数来完成的，如本例所示：
If the database contains multiple undo tablespaces, then you can optionally specify at startup that you want to use a specific undo tablespace.
This is done by setting the UNDO_TABLESPACE initialization parameter, as shown in this example:
UNDO_TABLESPACE = undotbs_01
如果初始化参数中指定的表空间不存在，则STARTUP命令失败。
UNDO_TABLESPACEE参数可用于将特定的UNDO表空间分配给Oracle Real Application Clusters环境中的实例。
If the tablespace specified in the initialization parameter does not exist, the STARTUP command fails.
The UNDO_TABLESPACE parameter can be used to assign a specific undo tablespace to an instance in an Oracle Real Application Clusters environment.
以下是撤消管理的初始化参数摘要：
The following is a summary of the initialization parameters for undo management:
UNDO_MANAGEMENT
如果为AUTO或null，则启用自动撤消管理。如果为MANUAL，则设置手动撤消管理模式。默认值为“AUTO”。
If AUTO or null, enables automatic undo management. If MANUAL, sets manual undo management mode. The default is AUTO.
UNDO_TABLESPACE
可选，仅在自动撤消管理模式下有效。指定撤消表空间的名称。
Optional, and valid only in automatic undo management mode. Specifies the name of an undo tablespace.
仅当数据库有多个undo表空间并且希望指示数据库实例使用特定的undo表空间时才使用。
Use only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.
注意：Oracle数据库的早期版本默认为手动撤消管理模式。
要更改为自动撤消管理，必须首先创建一个撤消表空间，然后将UNDO_MANAGEMENT初始化参数更改为AUTO。
如果您的Oracle数据库是Oracle9i或更高版本，并且要更改为自动撤消管理，请参阅《Oracle数据库升级指南》以获取说明。
Note:
Earlier releases of Oracle Database default to manual undo management mode.
To change to automatic undo management, you must first create an undo tablespace and then change the UNDO_MANAGEMENT initialization parameter to AUTO.
If your Oracle Database is Oracle9i or later and you want to change to automatic undo management, see Oracle Database Upgrade Guide for instructions.
在Oracle Database 11g及更高版本中，UNDO_MANAGEMENT初始化参数默认为null,即为自动撤消管理模式，但在早期版本中默认为手动撤消管理模式。
因此，将以前的版本升级到当前版本时必须小心。
《Oracle数据库升级指南》介绍了迁移到自动撤消管理模式的正确方法，包括有关如何调整撤消表空间大小的信息。
A null UNDO_MANAGEMENT initialization parameter defaults to automatic undo management mode in Oracle Database 11g and later,but defaults to manual undo management mode in earlier releases.
You must therefore use caution when upgrading a previous release to the current release.
Oracle Database Upgrade Guide describes the correct method of migrating to automatic undo management mode, including information on how to size the undo tablespace.
Managing Rollback/Undo Segments in AUM (Automatic Undo Management) (Doc ID 135090.1)
AUM下，不能在UNDO表空间下创建表或回滚段。
You cannot use undo tablespaces for other purposes than undo segments and you cannot perform any operation on system generated undo segments:
SQL> create table T (c number) tablespace undo_rbs1;
create table T (c number) tablespace undo_rbs1
*
ERROR at line 1:
ORA-30022: Cannot create segments in undo tablespace
SQL> create rollback segment undo_rs1 tablespace undo_rbs1;
create rollback segment undo_rs1 tablespace undo_rbs1
*
ERROR at line 1:
ORA-30019: RBU Rollback Segment operation not supported in SMU mode
Automatic Undo Management and Real Application Clusters
The Automatic Undo Management feature is also useful in Real Application Clusters environments.
All instances within Real Application Clusters environments must run in the same undo mode.
Set the global parameter UNDO_MANAGEMENT to AUTO in your server parameter file.
四：请描述对UNDO Retention的理解？
undo_retention表示已经提交或回滚的事物在UNDO EXTENT中保留的时间；
当事物结束时间 <= undo_retention时，UNDO EXTENT在dba_undo_extents.status中状态为UNEXPIRED;
当事物结束时间 > undo_retention时，UNDO EXTENT在dba_undo_extents.status中状态为EXPIRED;
当事物没有结束时 ， UNDO EXTENT在dba_undo_extents.status中 状态为 ACTIVE;
select t.status,sum(t.blocks)*8/1024||'M' from dba_undo_extents t group by t.status;
STATUS SUM(T.BLOCKS)*8/1024||'M'
--------- -----------------------------------------
UNEXPIRED 11.125M
EXPIRED 41.125M
ACTIVE 19.125M
The Undo Retention Period
undo retention period是Oracle数据库在覆盖以前尝试保留旧撤消信息的最短时间。
The undo retention period is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it.
About the Undo Retention Period
启用自动撤消管理时，总是有一个当前撤消保留期，这是Oracle数据库在覆盖以前尝试保留旧撤消信息的最短时间。
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.
事务提交后，回滚或事务恢复不再需要撤消数据。
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.
此外，几个Oracle Flashback功能的成功还取决于旧的撤消信息的可用性。
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.
旧的（已提交的）撤消信息早于当前撤消保留期，则表示该信息已过期，其空间可被新事务覆盖。
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.
旧的撤销信息的期限小于当前撤销保留期，则称为未过期，并保留用于一致的读取和Oracle闪回操作。
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.
Oracle数据库根据撤消表空间大小和系统活动自动调整撤消保留期。
Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity.
您可以选择通过设置UNDO_RETENTION初始化参数来指定最短的撤消保留期（以秒为单位）。
You can optionally specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter.
此参数对撤消保留的确切影响如下：
The exact impact this parameter on undo retention is as follows:
对于固定大小的UNDO表空间，将忽略UNDO_RETENTION参数。
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.
See "Automatic Tuning of Undo Retention" for more information.
对于启用了AUTOEXTEND选项的undo表空间，数据库将尝试遵守UNDO_RETENTION指定的最短保留期。
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.
如果为自动扩展撤消表空间指定了MAXSIZE子句，则当达到最大大小时，数据库可能会开始覆盖未过期的撤消信息。
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.
DBCA自动创建的UNDOTBS1表空间是自动扩展的。
The UNDOTBS1 tablespace that is automatically created by DBCA is auto-extending.
Automatic Tuning of Undo Retention
Oracle数据库根据undo表空间的配置方式自动调整undo保留期。
Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.
如果使用AUTOEXTEND选项配置undo表空间，则数据库会动态调整undo保留期，使其略长于系统上运行时间最长的活动查询。
If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system.
但是，此保留期可能不足以容纳Oracle Flashback操作。
However, this retention period may be insufficient to accommodate Oracle Flashback operations.
Oracle Flashback操作导致快照太旧错误，这表明您必须进行干预，以确保保留足够的撤消数据来支持这些操作。
Oracle Flashback operations resulting in snapshot too old errors are the indicator that you must intervene to ensure that sufficient undo data is retained to support these operations.
为了更好地适应Oracle Flashback功能，可以将UNDO_RETENTION参数设置为与预期Oracle Flashback操作的最长时间相等的值，也可以将UNDO表空间更改为固定大小。
To better accommodate Oracle Flashback features, you can either set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle Flashback operation, or you can change the undo tablespace to fixed size.
如果undo表空间的大小是固定的，则数据库会动态调整undo保留期，以获得该表空间大小和当前系统负载的最佳保留。
If the undo tablespace is fixed size, the database dynamically tunes the undo retention period for the best possible retention for that tablespace size and the current system load.
此最佳可能保留时间通常明显大于运行时间最长的活动查询的持续时间。
This best possible retention time is typically significantly greater than the duration of the longest-running active query.
如果决定将撤消表空间更改为固定大小，则必须选择足够大的表空间大小。
If you decide to change the undo tablespace to fixed-size, you must choose a tablespace size that is sufficiently large.
如果选择的撤消表空间太小，则可能会出现以下两个错误：
If you choose an undo tablespace size that is too small, the following two errors could occur:
DML可能会失败，因为没有足够的空间来容纳新事务的撤消数据。
DML could fail because there is not enough space to accommodate undo for new transactions.
长时间运行的查询可能会失败，并出现快照太旧的错误，这意味着没有足够的撤消数据来实现读取一致性。
Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.
注意：
LOB不支持自动调整撤消保留。
这是因为lob的undo信息存储在段本身中，而不是undo表空间中。
对于lob，数据库尝试遵守由undo_retention指定的最小undo保留期。
但是，如果空间变小，则可能会覆盖未过期的LOB undo信息。
Note:
Automatic tuning of undo retention is not supported for LOBs.
This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace.
For LOBs, the database attempts to honor the minimum undo retention period specified by UNDO_RETENTION.
However, if space becomes low, unexpired LOB undo information may be overwritten.
五：如何确保在undo_retention期间的数据不会被覆盖。
为了保证长时间运行的查询或Oracle闪回操作的成功，可以启用保留保证。
To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee.
如果启用了保留保证，则保证指定的最小撤消保留；
If retention guarantee is enabled, then the specified minimum undo retention is guaranteed;
数据库从不覆盖未过期的撤消数据，即使这意味着事务由于撤消表空间中的空间不足而失败。
the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.
如果未启用保留保证，则数据库可以在空间不足时覆盖未过期的撤消，从而降低系统的撤消保留。
If retention guarantee is not enabled, then the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system.
默认情况下禁用此选项。
This option is disabled by default.
警告：启用保留保证可能会导致多个DML操作失败。小心使用。
WARNING: Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.
在使用create DATABASE或create undo tablespace语句创建undo表空间时，可以通过为该表空间指定retention GUARANTEE子句来启用retention guarantee。
You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement.
或者，您可以稍后在ALTER TABLESPACE语句中指定此子句。
Or, you can later specify this clause in an ALTER TABLESPACE statement.
您可以使用RETENTION NOGUARANTEE禁用 retention guarantee。
You disable retention guarantee with the RETENTION NOGUARANTEE clause.
您可以使用DBA_TABLESPACES视图来确定undo表空间的retention guarantee设置。
You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace.
名为RETENTION的列包含值guarante、noguarante或NOT APPLY，其中NOT APPLY用于undo表空间以外的表空间。
A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY, where NOT APPLY is used for tablespaces other than the undo tablespace.
六：请描述ORA-01555错误原因和解决思路。
1. Read Consistency
构建一致性读时，需要的undo数据已经被覆盖。
通常原因如下：
(1)SQL语句执行时间太长。
(2)UNDO表空间过小。
(3)事务量过大。
(4)过于频繁的提交。
比如对一个块上的10行数据，每次修改1行并提交，就会对这个块生成10次UNDO镜像数据。
(5)导致执行SQL过程中进行一致性读时，SQL执行后修改的前镜像（即UNDO数据）在UNDO表空间中已经被覆盖，不能构造一致性读块（CR blocks）。
建议：
（1）增加UNDO表空间大小。
（2）增加undo_retention 时间，默认只有15分钟。
（3）优化出错的SQL，减少查询的时间，首选方法。
（4）避免频繁的提交。
2. Delayed Block Cleanout
SQL语句执行过程中，访问到的块，在进行延迟块清除时，不能确定该块的事务提交时间与SQL执行开始时间的先后次序。
发生延迟块清除情况下，如果一个块已被修改，下一个会话访问这个块时，可能必须查看最后一个修改这个块的事务是否还是活动的。一旦确定该事务不再活动，就会完成块清除。
Oracle会从块首部的ITL事务槽（该事务槽指向回滚段段头的事务槽），确定前一个事务所用的回滚段，然后试图从这个回滚段首部的事务槽来获得该事务的提交SCN。
如果事务的前镜像信息已经被覆盖，并且查询SCN也小于回滚段中记录的最小SCN，那么Oracle将无法判断查询SCN和事务提交SCN的大小，此时出现延迟块清除导致的ORA-01555错误。
Troubleshooting Assistant: Resolve Issues with Oracle Undo Management (ORA-01555, ORA-30036, ORA-01628, ORA-01552, etc.) (Doc ID 1575667.2)
ORA-1555 error with query duration 0 or less than UNDO_RETENTION
ORA-1555 error during export
ORA-1555 and ORA-22924 errors on LOB data
ORA-1555 error - generic troubleshooting
建议：
(1)减少查询的运行时间（调优）。
(2)保证使用的事务“大小适当”。确保没有不必要地过于频繁地提交。
(3)使用DBMS_STATS扫描相关的对象，加载之后完成这些对象的清理。由于块清除是极大量的UPDATE或INSERT造成的，所以很有必要这样做。
(4)允许UNDO表空间扩大，为之留出扩展的空间，并增加UNDO保持时间。
ORA-01555 "Snapshot too old" - Detailed Explanation (Doc ID 40689.1)
1. Read Consistency
这在Oracle数据库概念手册中有记录，因此将不作进一步讨论。
This is documented in the Oracle Database Concepts manual and so will not be discussed further.
但是，就本条而言，如果尚未理解，则应阅读并理解本条。
However, for the purposes of this article this should be read and understood if not understood already.
oracle server具有多版本读取一致性的能力，这对您来说是非常宝贵的，因为它可以保证您看到一致的数据视图（没有“脏读取”）。
Oracle Server has the ability to have multi-version read consistency which is invaluable to you because it guarantees that you are seeing a consistent view of the data (no 'dirty reads').
2. Delayed Block Cleanout
最好用一个例子来说明这一点：
This is best illustrated with an example:
考虑一个更新一百万行表的事务。
Consider a transaction that updates a million row table.
这显然会访问大量的数据库块来对数据进行更改。
This obviously visits a large number of database blocks to make the change to the data.
当用户提交事务时，Oracle不会返回并重新访问这些块以使更改永久化。
When the user commits the transaction Oracle does NOT go back and revisit these blocks to make the change permanent.
它留给访问受更新影响的任何块的下一个事务来“整理”块（因此称为“延迟块清理”）。
It is left for the next transaction that visits any block affected by the update to 'tidy up' the block (hence the term 'delayed block cleanout').
每当Oracle更改数据库块（索引、表、聚簇）时，它都会在数据块的头中存储一个指针，该指针标识用于保存事务所做更改的回滚信息的回滚段(如果用户以后选择不提交更改并希望“撤消”所做的更改，则需要此选项。）
Whenever Oracle changes a database block (index, table, cluster) it stores a pointer in the header of the data block which identifies the rollback segment used to hold the rollback information for the changes made by the transaction. (This is required if the user later elects to not commit the changes and wishes to 'undo' the changes made.)
提交时，数据库只是将相关的回滚段头条目标记为已提交。现在，当重新访问其中一个已更改的块时，Oracle会检查数据块的头，这表明它在某个点上已更改。数据库需要确认更改是已提交还是当前未提交。
Upon commit, the database simply marks the relevant rollback segment header entry as committed. Now, when one of the changed blocks is revisited Oracle examines the header of the data block which indicates that it has been changed at some point. The database needs to confirm whether the change has been committed or whether it is currently uncommitted.
为此，Oracle确定用于上一个事务的回滚段（来自块的头），然后确定回滚头是否指示它是否已提交。
To do this, Oracle determines the rollback segment used for the previous transaction (from the block's header) and then determines whether the rollback header indicates whether it has been committed or not.
如果发现该块已提交，则更新数据块的头，以便对该块的后续访问不会引起该处理。
If it is found that the block is committed then the header of the data block is updated so that subsequent accesses to the block do not incur this processing.
下面用一种非常简单的方式来说明这种行为。这里我们将介绍更新数据块所涉及的各个阶段。
This behaviour is illustrated in a very simplified way below. Here we walk through the stages involved in updating a data block.
ORA-1555错误可能发生在查询无法访问足够的undo以在查询开始时构建数据副本的情况下。
The ORA-1555 errors can happen when a query is unable to access enough undo to build a copy of the data at the time the query started.
块的已提交“版本”与这些块的较新未提交“版本”一起维护，以便查询可以访问数据库中在查询时存在的数据。
Committed "versions" of blocks are maintained along with newer uncommitted "versions" of those blocks so that queries can access data as it existed in the database at the time of the query.
这些被称为“一致读取”块，并使用Oracle撤消管理进行维护。
These are referred to as "consistent read" blocks and are maintained using Oracle undo management.
如何找到ORA-01555对应的SQL
How to find the complete SQL statement caused ORA-1555 :
If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from :
select SQL_TEXT from v$sqlarea where SQL_ID='<sql id from the error message>';
If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from :
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='<sql id from the error message>';
七：请描述LOB字段产生ORA-01555错误原因和解决思路。
LOB不支持自动调整撤消保留。
这是因为lob的undo信息存储在段本身中，而不是undo表空间中。
对于lob，数据库尝试遵守由undo_retention指定的最小undo保留期。
但是，如果空间变小，则可能会覆盖未过期的LOB undo信息。
LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)
有四种类LOB，分别是CLOB,BLOB,NCLOB,BFILE。
Background
There are four types of LOB:
CLOB, BLOB, NCLOB -> stored internally to Oracle
BFILE -> stored externally
包含LOB（CLOB、NCLOB和BLOB）的表会为每个LOB列创建两个额外的磁盘段—一个LOBINDEX和一个LOBSEMENT。
A table containing LOBs (CLOB, NCLOB and BLOB) creates 2 additional disk segments per LOB column - a LOBINDEX and a LOBSEGMENT.
These can be viewed, along with the LOB attributes, using the dictionary views:
DBA_LOBS, ALL_LOBS or USER_LOBS
可以允许LOB列在行中存储数据，也可以不存储，详情如下。
LOB columns can be allowed to store data within the row or not as detailed below.
是否允许in-line存储只能在创建时指定。
Whether in-line storage is allowed or not can ONLY be specified at creation time.
1.) INLINE LOBs
"STORE AS ( enable storage in row )"
允许LOB数据存储在表段中，前提是它小于约4000字节。
Allows LOB data to be stored in the TABLE segment provided it is less than about 4000 bytes.
实际的最大in-line LOB为3964字节。
The actual maximum in-line LOB is 3964 bytes.
In-line LOB受Oracle中的常规链接和行迁移规则的约束。
In-line LOBS are subject to normal chaining and row migration rules within Oracle.
Ie: If you store a 3900 byte LOB in a row with a 2K block size then the row piece will be chained across two or more blocks.
Undo Generation
对于In-Line LOBs，REDO和UNDO都是作为普通行数据的一部分写入的。
For In-Line LOBs Both REDO and UNDO are written as they are part of the normal row data.
"STORE AS ( disable storage in row )"
如果LOB数据大于3964字节，则LOB数据存储在LOB SEGMENT中（即：out of line）。
If the LOB data is greater than 3964 bytes, then the LOB data is stored in the LOB SEGMENT (ie: out of line).
out of line LOB的行为与“disable storage in row”下所述的相同，但如果其大小缩小到3964或更小，则可以再次inline LOB。
An out of line LOB behaves as described under 'disable storage in row' except that if its size shrinks to 3964 or less the LOB can again be stored inline.
行中存储了一个20字节的LOB locator，它为该列的LOB段中的LOB提供了唯一标识符。
A 20 byte LOB locator is stored in the ROW which gives a unique identifier for a LOB in the LOB segment for this column.
Lob Locator实际上向Lob索引提供一个键，该索引包含组成Lob的所有块（或页）的列表。
The Lob Locator actually gives a key into the LOB INDEX which contains a list of all blocks (or pages) that make up the LOB.
当一个LOB在'enable storage in row'LOB列中行外存储时，36到84字节之间的控制数据在行块中保持行内。
When a LOB is stored out-of-line in an 'enable storage in row' LOB column between 36 and 84 bytes of control data remain in-line in the row piece.
• LOBINDEX类似于Ｂ-tree的结构，存储各个LOB entry的LOB ID,LOB ID指向LOBSEGMENT中的实际存储区域。
• 如果要delete一条数据，删除的操作就是更新一下LOBINDEX, 并不会去将LOBSEGMENT中的内容写入回滚段中，这时候有少量的undo信息产生，但是是因为修改LOGINDEX产生的。
• 如果是update LOB，并不是去update原来的LOB entry，而是插入一条新的LOB entry,并且对LOB自身不产生undo信息,原来旧的数据仍然存放于LOBSEGMENT中（LOBSEGMENT中会存储相关的SCN信息）
• 这样query需要读取old version的数据就不是从回滚段中读取old value来重构，而是从LOBSEGMENT中读取原先的LOB entry。
• 如果delete,update操作很多，oracle会不会一直保存这些old version的数据呢？答案是不会。
• 这时就靠创建LOB时的参数PCTVERSION来控制了。PCTVERSION=10的含义就是在HWM下留有10%的空间用于存放Old version的数据.如果存放old version的空间多于PCTVERSION，那么就可以被重用。这时如果有query需要重构旧的数据，就会产生ORA-01555错误。想要避免01555的话一个就是尽量缩短query的时间，另外就是增大PCTVERSION，当然这会消耗更多的空间存放旧数据。
一般来说，普通的01555错误会指明发生01555的rollback segment,而LOB的则没有，而是伴随着ORA-22924出现。
例如：
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
解决方案：
oracle的lob大字段有自己的retention参数，如果只调整undo_retention，而没有同步到lob大字段，该参数还是默认的900s，确认查询结果如下：
select table_name,column_name,pctversion,retention from dba_lobs where table_name='T_CJC_001';
修改表含有lob大字段的retention设置
ALTER TABLE T_CJC_001 MODIFY LOB(CONTENT)(retention);
使用pctversion
从结果中可以看到，当显示指定pctversion的时候，retention参数会失效
alter table T_CJC_001 modify lob(CONTENT) (pctversion 10);
select column_name, pctversion, retention from user_lobs where table_name = 'T_CJC_001';
Undo Generation
UNDO只有在对列locator和LOB INDEX更改时写入。
UNDO is only written for the column locator and LOB INDEX changes.
不会为LOB段中的页面生成撤消。
No UNDO is generated for pages in the LOB SEGMENT.
使用页面版本可以实现一致的读取，即更新LOB的页面时，保留旧页面并创建新页面。
Consistent Read is achieved by using page versions i.e. When you update a page of a LOB the OLD page remains and a new page is created.
这可能会浪费空间，但旧页可以回收和重用。
This can appear to waste space but old pages can be reclaimed and reused.
Troubleshooting Steps
ORA-1555意味着我们得到的页面没有我们期望的（lob_id+版本），因此可能表示一致的读取问题或lob段本身的损坏。
ORA-1555 means the page we got to did not have the (lob_id + version) that we expected so can indicate a Consistent Read issue or a corruption on the lob segment itself.
V$undostat将不包含有用的信息，因为它不反映LOB undo信息。
V$undostat would not contain useful information because this does not reflect LOB undo information.
八：请描述ORA-30036错误原因和解决思路。
ORA-30036：unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace (Doc ID 460481.1)
When a ORA-30036 will be reported.
当当前UNDO表空间没有更多可用空间用于活动事务时，将报告ORA-30036错误。
ORA-30036 error is reported when the current Undo tablespace has no more free space available for the active transactions.
当事务访问数据库时，它们需要UNDO空间。UNDO空间分配按以下顺序进行：
When transactions hit a database and they need undo space. The undo space allocation happens in the following sequence:
1.在没有活动事务的UNDO段中分配数据块。Oracle尝试将事务分发到所有UNDO段。
Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments.
2.如果找不到UNDO段，则oracle会尝试将脱机UNDO段联机并使用它。
If no undo segment found then oracle tries to online an off-line undo segment and use it.
3.如果没有要联机的UNDO段，则创建一个新的UNDO段并使用它。
If no undo segments to online, then we create a new undo segment and use it.
4.如果空间不允许创建undo段，那么我们尝试重用现有undo段中过期的区段。
If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments.
对于与UNDO段/extent关联的正在运行的事务，如果它需要更多的UNDO空间，则：
For a running transaction associated with undo segment/ extent, if it needs more undo space then:
1.如果当前extent有更多可用块，则使用下一个已准备好分配给该extent的可用块。
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.如果下一个extent尚未过期，则从UNDO表空间获取空间。如果有可用的扩展数据块，则将其分配给UNDO段，并返回新扩展数据块中的第一个块。
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.如果没有可用的extent，则从脱机UNDO段进行窃取。从脱机UNDO段取消分配数据块，并将其添加到当前UNDO段。返回数据块的第一个空闲块。
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.从联机UNDO段窃取。从联机UNDO段取消分配数据块，并将其添加到当前UNDO段。返回数据块的第一个空闲块。
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.
注意：Bug2900863表示这个步骤在某些情况下是中断的。这在服务器补丁集9.2.0.5及更高版本和服务器版本10g中已修复。
NOTE: Bug 2900863 indicates this steps is broken in some cases. This is fixed in Server patchset version 9.2.0.5 and above, and in Server release 10g.
6.在UNDO表空间中扩展文件。如果文件可以扩展，则向当前UNDO段添加一个区段，然后返回块。
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.否则，尝试从自己的UNDO段重用未过期的区段。如果所有扩展数据块当前都很忙（它们包含未提交的信息），请转至步骤8。否则，请换行到下一个扩展数据块。
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.从脱机UNDO段随机窃取未过期的数据块。如果失败，则尝试联机UNDO段以供重用。
Randomly steal unexpired extents from offline undo segments. If this fails then try to online undo segments for reuse.
9.如果上述操作都失败，则返回ORA-30036无法将段扩展%s（在UNDO表空间“%s”中）
If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'
这个错误说明我们需要添加更多的空间来UNDO表空间，这可能并不总是这样。
This Error states that we need to add more space to Undo Tablespace, which might not be case always.
如果UNDO的大小太小，并且即使从其他UNDO段进行窃取，事务也无法获取UNDO块，则会发生窃取失败。
Failure to steal occurs where the undo is sized too small and the transaction is unable to get undo blocks even by stealing from other undo segments.
Troubleshooting ORA-30036 Errors
i) Check free space in the undo tablespace.
select sum(bytes) from dba_free_space where tablespace_name='<undo tablespace>';
select sum(bytes) from dba_data_files where tablespace_name='<undo tablespace>';
ii) Check whetherUndo tablespace datafile is autoextensible.
select autoextensible from dba_data_files where tablespace_name='<undo tablespace>;
iii) Check whether unexpired extents are available in the same segment as the current transaction.
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
如果没有剩余的UNDO空间，那么我们尝试使用未过期的区段（执行UNDO保留所需的UNDO区段）。
这有时会导致ORA-1555错误。
现在，如果没有未过期的扩展数据块，则需要添加空间来UNDO表空间。
In case no undo space is left, then we try to use unexpired extents (Undo Extent required to honour UNDO_RETENTION).
This sometimes results in ORA-1555 errors.
Now if you do not have unexpired extents also, then you need to add space to undo tablespace.
iv) Check the status of the Undo extents.
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*),TABLESPACE_NAME FROM DBA_UNDO_EXTENTS GROUP BY STATUS,TABLESPACE_NAME;
The actions depends based on the output:
情况1：没有可用的过期扩展数据块，并且大部分是活动扩展数据块。
Case 1: No Expired extents available and mostly Active extents present.
如果没有可重复使用的过期数据块，则可能会遇到ORA-30036。
如果我们看到大部分活动区，那么这很可能是UNDO大小调整问题。
在这种情况下，请检查UNDO表空间的大小是否正确。
If there are no Expired extents that can be re-used then its possible to encounter ORA-30036.
If we see mostly Active extents then this is most likely Undo sizing issue.
In this case, check if Undo Tablespace is correctly sized.
以下查询计算所需的字节数（基于当前工作负载）：
The following query calculates the number of bytes needed (based on the current workload):
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
*（UR）以秒为单位UNDO保留
*（UPS）每秒生成的UNDO数据块数
*（DBS）开销根据范围和文件大小（db_block_size）而变化
*(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)
Refer to below note for more explanation on this
How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)
SQL> SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks
SQL> SELECT (UR * (UPS * DBS)) AS "Bytes"
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'));
For 10g and Higher Versions where Tuned undo retention is being used,please use below query:
SQL>SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (select max(tuned_undoretention) AS UR from v$undostat),
(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'));
情况2：没有可用的过期扩展数据块，并且大部分未过期的扩展数据块都存在。
Case 2: No Expired extents available and mostly Unexpired extents present.
如果没有可重复使用的过期数据块，则可能会遇到ORA-30036。
如果我们看到的大部分是未过期的数据块，则可能是UNDO间距问题，也可能是由于UNDO保留率过高造成的。
换言之，这意味着UNDO空间不足以用于指定的UNDO保留或调整的UNDO保留值。
If there are no Expired extents that can be re-used then its possible to encounter ORA-30036.
If we see mostly Unexpired extents then it can be either a Undo spacing issue or caused by unreasonably high Undo retention. In other words, this means the Undo space is not enough for the specified Undo_Retention or the Tuned_undoretention value.
Check the undo retention and the tuned_undo retention
Show parameter Undo
select max(tuned_undoretention) from v$undostat;
如果保留率太高，则解决方法将是（以下任意一种）：
If the tuned_undoretention is too high the workarounds will be (any one of the following):
a. 启用UNDO表空间数据文件的自动扩展性，并将MAXSIZE设置为UNDO表空间中所有数据文件的实际大小。
Turn on autoextensibility of the undo tablespace datafiles and set the MAXSIZE to the actual size of the all the datafiles of the undo tablespace.
这改变了用于计算调整后的保留的算法，因此调整后的保留值设置与实际需求相比不会太高。
This alters the algorithm used for calculating tuned_undoretention and thus the tuned_undoretention values set wont be too high compared to the actual requirements.
b. Set _smu_debug_mode=33554432
这又一次改变了用于计算tuned_undoretention的算法，因此tuned_undoretention设置的值与实际需求相比不会太高。
This again alters the algorithm used for calculating tuned_undoretention and thus the tuned_undoretention values set wont be too high compared to the actual requirements.
c. 将_first_spare_parameter参数（10.2）或_highthreshold_undoretention（11.x）实例参数设置为限制优化的UNDO保留值的值。
set the _first_spare_parameter (10.2) or _highthreshold_undoretention (11.x) instance parameter to a value limiting the tuned undo retention value.
此值用作调整的保留时间的上限
This value acts as an upper limit for the tuned_undoretention
d. 通过设置_undo_autotune=false禁用UNDO的自动调整
Disable automatic tuning of undo by setting _undo_autotune=false
有关详细信息，请参阅自动调整UNDO保留常见问题（Doc ID 1579779.1）。
Refer Automatic Tuning of Undo Retention Common Issues (Doc ID 1579779.1) for details.
情况3：有很多过期的扩展数据块可用。
Case 3: Lot of Expired extents are available.
如果存在过期的扩展数据块，但仍然遇到ORA-30036，则表示未重用过期的扩展数据块。
If Expired extents are present and still ORA-30036 is encountered , it means that the EXPIRED extents are not being reused.
这些过期的扩展数据块应该被重用，相反，我们得到的是ORA-30036错误。
These Expired extents should have been reused and instead we are getting ORA-30036 error.
这可能是因为10.2.0.4（和11g）中修复了未发布的错误5442919。
This could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and 11g ).
Bug 5442919 - Expired extents not being reused (ORA-30036) (Doc ID 5442919.8)
如果在其他版本上发生这种情况，请向Oracle支持部门报告，以便进一步调查
If this is happening on other versions, then please report the same to Oracle Support for further investigation
九：当UNDO表空间不足时，如何将相关信息记录在告警日志中。
有时UNDO表空间不足时，相关信息没有记录在告警日志中，只是打印在前台。
ORA-30036 not logged in alert log when generated. (Doc ID 444106.1)
为什么没有记录到告警日志中?
Why is ORA-30036 generated but not logged in the alert log ?
根据审查代码时的进展情况，有许多地方发布了ORA-30036，因为这些地方都不提供将消息写入警报日志的任何工具。
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的编码非常明确，不能写入警报日志。
ORA-30036 is very specifically coded not to be written to the alert log.
这不是一个bug，因为内部决定不应该警告这个错误。如果在警报日志文件中看到此错误，则它将与其他错误一起出现。
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.
这在未发布的BUG 2099510中进行了讨论，它被关闭为“非BUG”。
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
ORA-30036如何记录到后台告警日志？
Oracle提供接口用于诊断Oracle的错误信息。
诊断事件可以在Session级设置，也可以在系统级设置，通常如果要诊断全局错误，最好在系统级设置。
Errorstack事件：dump 错误栈信息，通常Oracle发生错误时前台进程将得到一条错误信息，但某些情况下得不到错误信息，可以采用这种方式得到Oracle错误。
Errorstack 和 oradebug 命令类似，都可以帮助诊断错误信息。
打开ORA-30036的errorstack
alter system set events '30036 trace name errorstack level 3';
---alter system set events='30036 trace name errorstack off';
表示当出现ORA-30036错误时，dump 错误栈和进程栈。
或者使用oradebug：
--启用
SQL> oradebug event 30036 trace name errorstack level 10;
--关闭
SQL> oradebug event 30036 trace name errorstack off;
SQL> oradebug setmypid
SQL> oradebug tracefile_name
十：如何评估所需UNDO大小？
How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)
调整UNDO表空间的大小需要三个数据。
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)
所需的undo空间计算如下：
The undo space needed is calculated as:
UndoSpace = UR * (UPS * DBS)
以下公式计算每秒生成的峰值undo块：
The following formula calculates the peak undo blocks generated per second:
SELECT undoblks / ((end_time - begin_time) * 86400) "Peak Undo Block Generation"
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat);
列结束时间和开始时间是日期数据类型。
减去日期数据类型后，结果值为两个日期之间的天数。
要将天转换为秒，需要将86400乘以一天中的秒数（24小时*60分钟*60秒）。
Column END_TIME and BEGIN_TIME are DATE data types.
When DATE data types are subtracted, the resulting value is the # of days between both dates.
To convert days to seconds, you multiply by 86400, the number of seconds in a day (24 hours * 60 minutes * 60 seconds).
以下查询计算处理峰值撤消活动所需的字节数：
The following query calculates the number of bytes needed to handle a peak undo activity:
SELECT (UR * (UPS * DBS)) AS "Bytes"
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'));
对于使用调优撤消保留的10g及更高版本，请使用以下查询：
For 10g and Higher Versions where Tuned undo retention is being used,please use below query:
SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (select max(tuned_undoretention) AS UR from v$undostat),
(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'));
十一：请描述处理过UNDO相关的ORA-600错误。
ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []
ORA-600 [4193] When Trying To Open The Database (Doc ID 763566.1)
Single instance
For Rac Instance(If one instance is down and other is up and running)
For 8i database and Below
Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)
Basic Steps to be Followed While Solving ORA-00600 [4194]/[4193] Errors Without Using Unsupported parameter (Doc ID 281429.1)
SOLUTION
检测到重做记录和回滚（撤消）记录之间不匹配。
A mismatch has been detected between Redo records and rollback (Undo) records.
ARGUMENTS:
Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block
场景1：单实例
Step 1
--------
SQL> Startup nomount ; --> using spfile
SQL> Create pfile='/temp/<corrupt_pfile>.ora' from spfile ;
SQL> Shutdown immediate;
Step 2
-------
Modify the <corrupt_pfile>.ora and set Undo_management=Manual
SQL> Startup mount pfile='/temp/<corrupt_pfile>.ora'
SQL> Show parameter undo
it should show manual
SQL> Alter database open ;
If it comes up
SQL> Create rollback segment r01 ;
SQL> Alter rollback segment r01 online ;
Create a new undo tablespace
SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;
Please note :- 谨慎删除旧的undo表空间
You can delay the drop of the Old undo tablespace this is just to allow the block cleanout to happen for dead transaction.
So the below step can be issued after database has been up and running with new undo tablespace for couple of hours.
Also note if your database has been forced open(datafiles are not in sync and archive logs missing ) using any unsupported method then please do not drop the Old undo.
Drop the Old undo tablespace
SQL> Drop tablespace <undo tablespace name> including contents and datafiles
Step 3
-------
SQL> Shutdown immediate;
SQL> Startup nomount ; ---> Using spfile
SQL> Alter system set undo_tablespace=<new Undo tablespace created> scope=spfile;
SQL> Shutdown immediate ;
SQL> Startup
Check if error is reported
场景2：RAC数据库
For Rac Instance(If one instance is down and other is up and running)
------------------------
If one node is up and running and other node is failing with ORA-00600[4194]/[4193] then
From the instance which is up and running create a new undo tablespace and make it the default one for the other instance which is down with the error.
Startup the failing instance with the new undo tablespace.
From Instance which is up and running
Create undo tablespace undo_new datafile '<filename>' size <> m ;
Alter system set undo_tablespace=<New undo tablespace name> sid=<instance which has corrupt undo tablespace and is down> scope=spfile ;
Now Startup the Instance which is down
SQL>Startup mount
SQL>Show parameter undo
Should show the new undo tablespace created above
SQL>Alter database open ;
SQL>Drop tablespace <Old undo tablespace of the failing instance> including contents and datafiles
If all the Instance is down in the Rac due to this error then following the instruction given for Single instance and create new undo tablespace.
场景3：数据库版本<=8i
For 8i database and Below
SQL>Startup restrict
Drop the Manual rollback segments and recreate it
场景4：system作为undo表空间时
@Option 3(System undo segment erroring with Ora-00600[4194/4193]
@Option 1 would fail if the undo segment involved is System undo segment.
@Please refer the note given below for patching the same.
@Note.452620.1 :Int/Pub ORA-600 [4193] ORA-600 [4194] IN SYSTEM ROLLBACK SEGMENT. HOW TO @PATCH
和UNDO有关的几个隐含参数
(1)_corrupted_rollback_segments
(2)_offline_rollback_segments
不建议使用 _corrupted_rollback_segments、_offline_rollback_segments
十二：如何限制指定用户使用UNDO大小。
建立撤消空间的用户配额
Establishing User Quotas for Undo Space
可以使用Oracle数据库资源管理器为UNDO空间建立用户配额。
数据库资源管理器指令UNDO_POOL允许dba限制一组用户（资源使用者组）消耗的撤消空间量。
You can use the Oracle Database Resource Manager to establish user quotas for undo space.
The Database Resource Manager directive UNDO_POOL allows DBAs to limit the amount of undo space consumed by a group of users (resource consumer group).
可以为每个使用者组指定撤消池。撤消池控制使用者组可以生成的撤消总量。
You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group.
当使用者组生成的撤消总数超过其撤消限制时，将终止生成撤消的当前更新事务。
When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the undo is terminated.
在从池中释放撤消空间之前，使用者组的其他成员不能执行进一步的更新。
No other members of the consumer group can perform further updates until undo space is freed from the pool.
如果没有显式定义UNDO_POOL指令，则允许用户使用无限的UNDO空间。
When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.
十三：如果设置Temporary Undo。
管理临时撤消
Managing Temporary Undo
默认情况下，临时表的undo记录存储在undo表空间中，并记录在redo中，这与持久表的undo管理方式相同。
By default, undo records for temporary tables are stored in the undo tablespace and are logged in the redo, which is the same way undo is managed for persistent tables.
但是，可以使用TEMP_UNDO_ENABLED初始化参数将临时表的UNDO与持久表的UNDO分开。
However, you can use the TEMP_UNDO_ENABLED initialization parameter to separate undo for temporary tables from undo for persistent tables.
当此参数设置为TRUE时，临时表的撤消操作称为临时撤消。
When this parameter is set to TRUE, the undo for temporary tables is called temporary undo.
About Managing Temporary Undo
临时撤消记录存储在数据库的临时表空间中，因此不会记录在重做日志中。
Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log.
启用临时撤消时，临时表空间使用的某些段存temporary undo，这些段称为临时撤消段temporary undo segments。
When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called temporary undo segments.
启用临时撤消后，可能需要增加临时表空间的大小，以考虑撤消记录。
When temporary undo is enabled, it might be necessary to increase the size of the temporary tablespaces to account for the undo records.
启用临时撤消可提供以下好处：
Enabling temporary undo provides the following benefits:
临时撤消减少了存储在UNDO表空间中的撤消量。
Temporary undo reduces the amount of undo stored in the undo tablespaces.
撤消表空间中的撤消次数越少，撤消记录的撤消保留期要求就越现实。
Less undo in the undo tablespaces can result in more realistic undo retention period requirements for undo records.
临时撤消减少重做日志的大小。
Temporary undo reduces the size of the redo log.
性能得到了提高，因为写入重做日志的数据更少，而解析重做日志记录的组件（如LogMiner）的性能更好，因为要解析的重做数据更少。
Performance is improved because less data is written to the redo log, and components that parse redo log records, such as LogMiner, perform better because there is less redo data to parse.
临时撤消使用Oracle Active data Guard选项对物理备用数据库中的临时表启用数据操作语言（DML）操作。
但是，创建临时表的数据定义语言（DDL）操作必须在主数据库上发出。
Temporary undo enables data manipulation language (DML) operations on temporary tables in a physical standby database with the Oracle Active Data Guard option.
However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.
可以为特定会话或整个系统启用临时撤消。
You can enable temporary undo for a specific session or for the whole system.
使用ALTER session语句为会话启用临时撤消时，会话将创建临时撤消，而不会影响其他会话。
When you enable temporary undo for a session using an ALTER SESSION statement, the session creates temporary undo without affecting other sessions.
使用ALTER system语句为系统启用临时撤消时，所有现有会话和新会话都将创建临时撤消。
When you enable temporary undo for the system using an ALTER SYSTEM statement, all existing sessions and new sessions create temporary undo.
当会话第一次使用临时对象时，将为会话的其余部分设置
TEMP_UNDO_ENABLED初始化参数的当前值
When a session uses temporary objects for the first time, the current value of the TEMP_UNDO_ENABLED initialization parameter is set for the rest of the session.
因此，如果为会话启用了临时撤消，并且会话使用临时对象，则不能为会话禁用临时撤消。
Therefore, if temporary undo is enabled for a session and the session uses temporary objects, then temporary undo cannot be disabled for the session.
类似地，如果对会话禁用临时撤消，并且会话使用临时对象，则不能对会话启用临时撤消。
Similarly, if temporary undo is disabled for a session and the session uses temporary objects, then temporary undo cannot be enabled for the session.
对于具有Oracle Active Data Guard选项的物理备用数据库，默认情况下会启用临时撤消。
Temporary undo is enabled by default for a physical standby database with the Oracle Active Data Guard option.
由于默认设置，TEMP_UNDO_ENABLED初始化参数对具有活动数据保护选项的物理备用数据库没有影响。
The TEMP_UNDO_ENABLED initialization parameter has no effect on a physical standby database with Active Data Guard option because of the default setting.
Enabling and Disabling Temporary Undo
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;
ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;
十四：DML(insert/delete/update)哪种操作产生的UNDO最多。
insert操作回滚段中只记录这些记录的ROWID，产生的UNDO最少。
delete操作记录了所有删除的记录的详细信息，以便于rollback，产生的UNDO最多。
update操作需要记录相关字段的undo信息 。
十五：请介绍UNDO常用的数据字典和视图有哪些。
Undo Space Data Dictionary Views
在自动撤消管理模式下，可以查询一组视图以获取有关撤消空间的信息。
You can query a set of views for information about undo space in the automatic undo management mode.
除了这里列出的视图之外，您还可以从视图中获取信息，这些视图可用于查看表空间和数据文件信息。
In addition to views listed here, you can obtain information from the views available for viewing tablespace and data file information.
See "Data Files Data Dictionary Views" for information on getting information about those views.
以下动态性能视图可用于获取有关撤消表空间的空间信息：
The following dynamic performance views are useful for obtaining space information about the undo tablespace:
(1)V$UNDOSTAT
包含用于监视和调整撤消空间的统计信息。
Contains statistics for monitoring and tuning undo space.
使用此视图可以帮助估计当前工作负载所需的撤消空间量。
Use this view to help estimate the amount of undo space required for the current workload.
数据库还使用此信息来帮助调整系统中的撤消使用情况。
The database also uses this information to help tune undo usage in the system.
此视图仅在自动撤消管理模式下才有意义。
This view is meaningful only in automatic undo management mode.
(2)V$TEMPUNDOSTAT
包含用于监视和调整临时撤消空间的统计信息。
Contains statistics for monitoring and tuning temporary undo space.
使用此视图可以帮助估计当前工作负载的临时表空间中所需的临时撤消空间量。
Use this view to help estimate the amount of temporary undo space required in the temporary tablespaces for the current workload.
数据库还使用这些信息来帮助调整系统中临时撤消的使用。
The database also uses this information to help tune temporary undo usage in the system.
只有启用临时撤消时，此视图才有意义。
This view is meaningful only when temporary undo is enabled.
(3)V$ROLLSTAT
对于自动撤消管理模式，信息反映撤消表空间中撤消段的行为。
For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace.
(4)V$TRANSACTION
Contains undo segment information
包含撤消段信息
(5)DBA_UNDO_EXTENTS
显示撤消表空间中每个扩展数据块的状态和大小。
Shows the status and size of each extent in the undo tablespace.
(6)DBA_HIST_UNDOSTAT
包含V$UNDOSTAT信息的统计快照。
Contains statistical snapshots of V$UNDOSTAT information.
V$UNDOSTAT视图对于监视事务执行对当前实例中undo空间的影响非常有用。
The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance.
统计信息可用于实例中的撤消空间消耗、事务并发性、撤消保留的优化以及长时间运行的查询的长度和SQL ID。
Statistics are available for undo space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.
视图中的每一行都包含实例中每隔10分钟收集的统计信息。
Each row in the view contains statistics collected in the instance for a ten-minute interval.
行按“BEGIN_TIME”列值降序排列。
The rows are in descending order by the BEGIN_TIME column value.
每一行都属于由标记的时间间隔(BEGIN_TIME, END_TIME)。
Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME).
每列表示在该时间间隔内为特定统计信息收集的数据。
Each column represents the data collected for the particular statistic in that time interval.
视图的第一行包含（部分）当前时间段的统计信息。
The first row of the view contains statistics for the (partial) current time period.
该视图总共包含576行，周期为4天。
The view contains a total of 576 rows, spanning a 4 day cycle.
十六：请介绍UNDO常用查询语句。
(1)查看UNDO基础信息
Show parameter Undo
select sum(bytes) from dba_free_space where tablespace_name='<undo tablespace>';
select sum(bytes) from dba_data_files where tablespace_name='<undo tablespace>';
select sum(blocks) "UNEXPIRED BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='UNEXPIRED';
select sum(blocks) "EXPIRED BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='EXPIRED';
select sum(blocks) "ACTIVE BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='ACTIVE';
select count(*) from dba_rollback_segs where status='OFFLINE';
select max(tuned_undoretention) from v$undostat;
(2)查看Retention Guarantee
select tablespace_name,
block_size,
extent_management,
segment_space_management,
contents,
retention
from dba_tablespaces;
(3)查看当前各类型extent使用占比
select tablespace_name, status, round(sum(bytes) / 1024 / 1024, 1) size_m
from dba_undo_extents
group by tablespace_name, status
order by 1, 3;
ACTIVE - Undo Extent is Active, Used by a transaction.
EXPIRED - Undo Extent is expired (Exceeded the Undo Retention).
UNEXPIRED - Undo Extent will be required to honor UNDO_RETENTION.
(4)表空间使用率
set pagesize 9999
set pagesize 9999
set linesize 132
select a.tablespace_name,
a.Total_mb,
f.Free_mb,
round(a.total_MB - f.free_mb, 2) Used_mb,
round((f.free_MB / a.total_MB) * 100) "%_Free"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total_MB
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free_MB
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "%_Free";
select tablespace_name, round(sum(bytes) / 1024 / 1024, 1) size_m
from dba_data_files
where tablespace_name like 'UNDO%'
group by tablespace_name
order by 2;
(5)扩容百分比
set pagesize 9999
set pagesize 9999
set linesize 132
select a.tablespace_name,
a.Total_mb,
f.Free_mb,
' Add' ||
to_char((total_MB * (1 - PCT / 100) - Free_mb) / (PCT / 100) / 1024,
'9990.9') || 'GB to ' || PCT || '%' "Add_Size(GB)"
from (select (&pct) PCT from dual),
(select tablespace_name, sum(bytes / (1024 * 1024)) total_MB
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free_MB
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "Add_Size(GB)";
(6)查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) InitialExtent,
(next_extent / 1024) NextExtent,
max_extents,
v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;
(7)前50使用最大undo的SESSION信息
set linesize 200 pagesize 1000
col module for a20 trunc
col username for a12 trunc
col sid for a12 trunc
select *from (
SELECT a.sid||'_'||a.serial# sid,a.username,a.sql_id,round(b.used_ublk*8/1024,1) size_m,b.used_urec,last_call_et, a.module FROM V$SESSION A,V$TRANSACTION B
WHERE A.TADDR=B.ADDR
AND A.TADDR IS NOT NULL
order by b.used_ublk desc)
where rownum<=50;
(8) 前20最大rollback segment
SELECT *
FROM ( SELECT segment_name,
status,
SUM (bytes) / 1024 / 1024 size_m,
COUNT (*)
FROM dba_undo_extents
WHERE tablespace_name = 'UNDOTBS2'
GROUP BY segment_name, status
ORDER BY size_m DESC)
WHERE ROWNUM <= 20;
(9) 前20活跃回滚段
SELECT *
FROM ( SELECT segment_name,
status,
SUM (bytes) / 1024 / 1024 size_m,
COUNT (*)
FROM dba_undo_extents
WHERE tablespace_name = 'UNDOTBS2' AND STATUS = 'ACTIVE'
GROUP BY segment_name, status
ORDER BY size_m DESC)
WHERE ROWNUM <= 20;
(10)过去7*24小时中UNDO表空间的平均使用量
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention,
dbs db_block_size,
((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
from v$undostat),
(select value as dbs from v$parameter where name = 'db_block_size');
(11)以下SQL语句则按峰值情况计算UNDO表空间所需空间
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention,
dbs db_block_size,
((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (undoblks / ((end_time - begin_time) * 86400)) ups
from v$undostat
where undoblks in (select max(undoblks) from v$undostat)),
(select value as dbs from v$parameter where name = 'db_block_size');
(12)查看某个事务正在用哪个undo segment
select s.sid,
s.serial#,
s.sql_id,
v.usn,
segment_name,
r.status,
v.rssize / 1024 / 1024 mb
From dba_rollback_segs r, v$rollstat v, v$transaction t, v$session s
Where r.segment_id = v.usn
and v.usn = t.xidusn
and t.addr = s.taddr
order by segment_name;
(13)查询回滚需要多久
select usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal - undoblocksdone "ToDo",
decode(cputime,
0,
'unknown',
sysdate + (((undoblockstotal - undoblocksdone) /
(undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;
(14) 查看_undo_autotune信息
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_undo_autotune';
(15) 查看UNDO信息
set pagesize 25
set linesize 100
column UNXPSTEALCNT heading "# Unexpired|Stolen"
column EXPSTEALCNT heading "# Expired|Reused"
column SSOLDERRCNT heading "ORA-1555|Error"
column NOSPACEERRCNT heading "Out-Of-space|Error"
column MAXQUERYLEN heading "Max Query|Length"
select inst_id,
to_char(begin_time, 'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT,
EXPSTEALCNT,
SSOLDERRCNT,
NOSPACEERRCNT,
MAXQUERYLEN,
TUNED_UNDORETENTION
from gv$undostat
order by inst_id, begin_time;
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON",
MAXQUERYLEN, TUNED_UNDORETENTION
FROM v$UNDOSTAT;
字段说明：
UNXPSTEALCNT
从其他撤消段窃取未过期的块以满足空间请求时的尝试次数
The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
UNXPBLKRELCNT
从撤消段中删除的、供其他事务使用的未过期块数
The number of unexpired blocks removed from undo segments to be used by other transactions
UNXPBLKREUCNT
事务重用的未过期撤消块数
The number of unexpired undo blocks reused by transactions
EXPSTEALCNT
从其他撤消段窃取过期扩展数据块以满足空间请求的尝试次数
The number of attempts when expired extents were stolen from other undo segments to satisfy a space requests
EXPBLKRELCNT
为满足空间请求而从其他撤消段窃取的过期扩展数据块数
The number of expired extents stolen from other undo segments to satisfy a space request
EXPBLKREUCNT
在相同撤消段中重用的过期撤消块数
The number of expired undo blocks reused within the same undo segments
SSOLDERRCNT
间隔期间发生的ORA-1555错误数
The number of ORA-1555 errors that occurred during the interval
NOSPACEERRCNT
空间不足错误数
The number of Out-of-Space errors
当UNXPSTEALCNT到EXPBLKREUCNT列包含非零值时，表示空间压力。
When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.
如果列ssoldercnt不为零，则未正确设置UNDO_RETENTION。
If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.
如果NOSPACEERRCNT列不为零，则存在严重的空间问题。
If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.
在10g DBA_HIST_UNDOSTAT视图中，包含V$UNDOSTAT信息的统计快照。
In 10g DBA_HIST_UNDOSTAT view contains statistical snapshots of V$UNDOSTAT information.
注意：如果参数_undo_autotune=FALSE，则在表X$KTUSMST2中不会生成数据，该表是视图DBA_HIST_UNDOSTAT视图中，包含V的源表
Note: If the parameter _undo_autotune=FALSE, there will be no data generated in table X$KTUSMST2 which is the source table of view dba_hist_undostats
With Oracle database 10g, you can use the Undo Advisor feature to get Undo sizing Recommendations
Refer: Check Current Undo Configuration and Advise Recommended Setup (Doc ID 1579035.1)
For database version 10g and above, use the following script. Ensure to execute the same as SYSDBA.
RDBMS version 10g and above:
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
- Undo Analysis started at : 30/08/2013 11:08:40 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 23/08/2013 11:08:40
End Time : 30/08/2013 11:08:40
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS2
Current undo tablespace size (datafile size now) : 20M
Current undo tablespace size (consider autoextend) : 20M
AUTOEXTEND for undo tablespace is : ON
Current undo retention : 900
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 26 MB
Undo Space Recommendation
-------------------------
Minimum Recommendation : Size undo tablespace to 26 MB
Rationale : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 26M
Retention Recommendation
------------------------
The best possible retention with current configuration is : 9171 Seconds
The longest running query ran for : 2543 Seconds
The undo retention required to avoid errors is : 2543 Seconds
PL/SQL procedure successfully completed.
日积月累
