ora-01555-CSDN博客

产生ORA-01555错误主要有三种可能性：
1、UNDO段对于系统所运行的工作负荷太小；
2、在循环过程中有事务提交是造成ORA-01555错误的最常见的原因；
3、block cleanout；

解决ORA-01555错误的解决方案有下面几种：
1、适当的设置UNDO_RETENTION参数值（大于事务执行可能需要的最长时间）；如果UNDO_RETENTION参数值变大，那么同时也需要根据果UNDO_RETENTION参数值大小修改UNDO表
   空间的大小也需要做相应的修改；（适合于UNDO管理是自动管理）
   网上查询UNDO表空间的大小可以根据公式：
   Undo Size = Undo_retention * UPS
   UPS是undo block per second, 我们可以通过V$UNDOSTAT. UNDOBLKS获得       .
   SQL> select avg(undoblks)/(10*60) UPS from v$undostat;
   UPS
   -------------
   0.03
   则undo_retention=10800，至少需要 10800*0.03=324个数据块。
2、如果是手动管理UNDO，增长UNDO段的大小或者增加UNDO段的的个数，这样做可以减少在查询延续的时间比较长的情况下UNDO数据被覆盖的可能性。这个解决方案可以解决上面
   的三种造成错误原因的情况；
3、减少查询时间，优化查询语句。如果可能的话，这是最好的方法，所以这是首要考虑的方法。因为减少查询时间可以减少对UNDO空间的需求。这个解决方案可以解决上面
   的三种造成错误原因的情况；
4、对相关的对象做下分析统计。因为这个方法会避免上面造成错误的第三种可能性：block cleanout的情况发生。因为block cleanout的情况经常是大量的更新或者插入操作
   的结果。所以在进行完大量的更新或者插入操作之后做下相关对象的分析统计是有必要的。

实验1：针对第二种最常见的情况和第一种可能情况：
--创建undo表空间时固定表空间的大小
create undo tablespace undo_small
datafile 'undo_small .dbf'
size 2m
autoextend off ；
--切换表空间为定义的小的undo表空间
alter system set undo_tablespace=undo_small;

drop table t

--创建测试表，并且要求数据是随机产生的
create table t
as
select *
from all_objects
order by dbms_random.random;

alter table t add constraint t_pk primary key(object_id);

--统计表
begin
dbms_stats.gather_table_stats('DAIMIN','T',cascade=>true);
end;

--大量更新操作
begin
for x in ( select rowid rid from t )
loop
update t set object_name = lower(object_name) where rowid = x.rid;
commit;
end loop;
end;
(注：采用循环更新提交比一次性更新提交要慢很多，即提交的次数多反而性能慢。即上面的更新语句比update t set object_name = lower(object_name)要慢很多。
有兴趣的可以测试下，这个不是这里主要讲的。)
--在上一个程序执行的同时，下面的语句在另一个会话中执行，则会报ORA-01555错误
declare
cursor c is
select /*+ first_rows */ object_name    --c游标中的数据集是按照object_id排好序的。
from t
order by object_id;
l_object_name t.object_name%type;
l_rowcnt number := 0;
begin
open c;
loop
fetch c into l_object_name;
exit when c%notfound;
dbms_lock.sleep( 0.01 );
l_rowcnt := l_rowcnt+1;
end loop;
close c;
exception
when others then
dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
raise;
end;

报错分析：
1、undo表空间太小；
2、因为在更新是一行一行按照原来插入数据的顺序地更新的，减少了对每个指定行更新前的查找记录的时间；相反，对于上面的查询来说，
因为不是按照原来插入数据的顺序查找数据的，而是按照自己object_id排好序的顺序查找数据的。所以下面的查询语句必定比上面的更
新语句执行的慢，所需要的时间也比较的长。这样就有可能发生这样的情况：当查询语句需要查一行数据时，但是该行数据已经被更新语
句在很久以前就已经被更新修改过并且已经提交掉，因为UNDO表空间很小，又由于更新采用的是循环更新操作，使得UNDO表空间被循环利用，
在循环中后面的更新语句所产生的UNDO数据会覆盖掉之前产生的UNDO数据，造成此时查询语句查找不到在查找开始的时间点上该行记录的
数据，则会报ORA-01555。
-----------------------------------------------------------------------------------------------------
对上面出现的问题进行解决需要做两件事情：
1、对UNDO_RETENTION参数需要设置的大点，设置为允许读数据进程全部完成所需要的最长时间；
2、在调大UNDO_RETENTION参数值同时，需要将undo_tablespace的大小也需要设置的大点；
如果是自动管理UNDO表空间，则设置为自动增长；
如果是手动管理UNDO表空间则分配更多的磁盘空间给UNDO表空间

对于上面的例子：
1）对于上面的查询需要的时间小于900s,执行时间大概为780s，通过
SQL> show parameter UNDO_RETENTION
NAME                                 TYPE        VALUE
----------------------------------- ----------- ------------------------------
undo_retention                       integer     900
查询UNDO_RETENTION原来的值为900s，UNDO_RETENTION参数值是足够的，所以先不需要修改；
2）由于原来的UNDO表空间为2M，是不可以自动增长的，所以设置UNDO表空间为自动增长，以1M的速度增长，并且最大设置为2G；
SQL> select file_name
2   from dba_data_files
3   where tablespace_name = 'UNDO_SMALL';
FILE_NAME
--------------------------------------------------------------------------------
C:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/UNDO_SMALL .DBF

alter database
datafile 'C:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/UNDO_SMALL .DBF'
autoextend on
next 1m
maxsize 2048m;
做了上面的修改操作之后，下面来重新同时执行上面的更新语句和查询语句，就不会有ORA-01555错误产生；

在AUM模式下，我们知道UNDO_RETENTION参数用以控制事务提交以后undo信息保留的时间。该参数以秒为单位，9iR1初始值为900秒，在Oracle9iR2增加为10800秒。但是这是一个NO Guaranteed的限制。
也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖。
很多时候这是不希望看到的。

从Oracle10g开始，如果你设置UNDO_RETENTION为0，那么Oracle启用自动调整以满足最长运行查询的需要。当然如果空间不足，那么Oracle满足最大允许的长时间查询。而不再需要用户手工调整。

同时Oracle增加了Guarantee控制，也就是说，你可以指定UNDO表空间必须满足UNDO_RETENTION的限制。

SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered

SQL> alter tablespace undotbs1 retention noguarantee;

Tablespace altered

在DBA_TABLESPACES视图中增加了RETENTION字段用以描述该选项:

SQL> select tablespace_name,contents,retention from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
SYSTEM                         PERMANENT NOT APPLY
UNDOTBS1                       UNDO      NOGUARANTEE
SYSAUX                         PERMANENT NOT APPLY
TEMP                           TEMPORARY NOT APPLY
USERS                          PERMANENT NOT APPLY
EYGLE                          PERMANENT NOT APPLY
TEST                           PERMANENT NOT APPLY
ITPUB                          PERMANENT NOT APPLY
TRANS                          PERMANENT NOT APPLY
BIGTBS                         PERMANENT NOT APPLY
TEMP2                          TEMPORARY NOT APPLY
TEMP03                         TEMPORARY NOT APPLY
DFMBRC                         PERMANENT NOT APPLY
T2K                            PERMANENT NOT APPLY

14 rows selected

在Oracle官方文档上这样解释:
RETENTION Undo tablespace retention:
GUARANTEE - Tablespace is an undo tablespace with RETENTION specified as GUARANTEE

A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail.

分类: OralceRac

本文转自einyboy博客园博客，原文链接：http://www.cnblogs.com/einyboy/archive/2012/06/11/2544779.html