APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
ORA-1555 or snapshot too old error reported on a LOB segment. The error message looks like:
ORA-01555: snapshot too old: rollback segment number with name "" too small
and sometimes followed by ORA-22924 error.
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
CAUSE
LOB data doesn't use the Undo segment for retaining the read consistent images. The old versions are stored in the LOB Segments itself, before any DMLs.
The ORA-01555 on a LOB segment is reported generally in two cases:
a) The query is accessing a LOB segment which is corrupted
OR
b) Read consistent images of the LOB data is not available in the LOB Segment. This happens due to the wrong setting of PCTVERSION / RETENTION attributes of the LOB segment.
SOLUTION
1) The first step to resolve ORA-1555 on LOB column is to check for corruption. This is the most common case.
1.a) Create a dummy table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"
SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);
1.b) Find the column names containing LOB data. You can DESCRIBE the table encountering the error and note down the columns names with datatype CLOB and BLOB.
SQL> DESC LOBDATA
Name Null? Type
---------- --------- ------------
ID NOT NULL NUMBER
DOCUMENT BLOB
1.c) Execute the following PL/SQL block to identify the corrupted rows. Ensure to replace <lob Column> and <Table name> with the respective LOB column and table name.
declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
begin
num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/
After running the above procedure, it prompts for:
Enter value for lob_column : EMP_XML
Enter value for table_owner : SCOTT
Enter value for table_with_LOB: EMP
1.d) After executing the above procedure, the table “CORRUPTED_LOB_DATA” will contain the rowids of the corrupted rows.
select * from corrupted_lob_data;
1.e ) If you have multiple LOB columns in the same table or want to check multiple tables, please execute the above steps again.
If there are no corrupted rows found, please skip the next step and go to Step 4.
2) Once the corruption is identified, we need to get rid of it to resolve the ORA-1555 error. Its difficult to identify the cause of the corruption unless we have the steps to reproduce the corruption at will. So, the next steps will be to salvage the data and resolve ORA-1555 error.
2.a) Restore and recover the LOB segment using physical backup.
OR
2.b) Empty the affected LOBs using the UPDATE statement
update <owner>.<tablename>
set <lob column> = empty_blob()
where rowid in (select corrupted_rowid from corrupted_lob_data);
commit;
Eg:
update LOBDATA set document = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);
PS: for BLOB and BFILE columns use EMPTY_BLOB() and for CLOB and NCLOB columns use EMPTY_CLOB()
OR
2.c) Export the table without the corrupted row, like:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_satc.dmp LOGFILE=expdp_satc.log TABLES=tc.lobdata QUERY=\"WHERE rowid NOT IN \(\'<rowid's listed in corrupt_rowid>\'\)\"
Example using DataPump export:
#> expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
Example using conventional export:
#> exp scott/tiger file=test.dmp log=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
This step is helpful to resolve the ORA-1555 error during Export or while taking backup.
OR
2.d) Delete the corrupted rows and then get the data from any other source (like standby database, flat file, or other databases etc) , if its available.
3). After resolving the corruption, upgrade your database to the latest version (or atleast above 10.2.0.4) as there are some bugs reported in the older version, which cause LOB corruption.
If corruption is resolved, you can skip Step 4.
4) If there is no corrupted rows found, then the solution is to ensure the read consistent images are retained in the LOB segment for adequate duration. You can increase either the RETENTION or the PCTVERSION attribute of the LOB column
4.a) Till 11gR1, the RETENTION attribute of the LOB segment will be equal to the UNDO_RETENTION parameter. Hence we recommend to set UNDO_RETENTION to the maximum of the duration of the queries in the database. Check the maxquerylen:
SQL> select max(maxquerylen) from v$UNDOSTAT;
and set this value for UNDO_RETENTION parameter
alter system set UNDO_RETENTION=<value in seconds>
4.b) From 11gR2 and above, we can set the retention for the LOB column to a value other than the UNDO_RETENTION parameter:
SQL> alter table <Table with LOBS causing ORa-1555> modify lob(<lobcolumn name>) (retention);
Verify the same:
SQL> select retention from dba_lobs where table_name='<Table with LOBS causing ORa-1555>';
OR
4.c) Increase the PCTVERSION attribute of the LOB segment
SQL> alter table <tablename> modify lob(<lob column name>) (pctversion 50);
Higher values of PCTVERSION will ensure the more space is allocated for the old versions of LOB data.
For more details refer to the troubleshooting guide:
Troubleshooting ORA-01555 - Snapshot Too Old: Rollback Segment Number "String" With Name "String" Too Small(Doc ID 1580790.1)
Troubleshooting ORA-01555/ORA-01628/ORA-30036 During Export and Import(Doc ID 1579437.1)
- 浏览: 417908 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (267)
- 序列号 (1)
- jquery (14)
- Eclipse插件 (3)
- Flex (2)
- Rose (1)
- Myeclipse (3)
- maven (4)
- SpringSecurity (2)
- sproutcore (1)
- Http (2)
- svn (4)
- gwt (3)
- jetty (1)
- freebsd (1)
- java (29)
- Oracle (21)
- windows (1)
- Mybatis3.0 (3)
- Struts2 (6)
- easyui (1)
- JPA (1)
- UML (1)
- dom4j (1)
- design pattern (2)
- JavaScript (8)
- FreeMarker (2)
- SQLServer (1)
- memcached (3)
- urlrewrite (1)
- MonggoDB (1)
- Solr (1)
- Lucene (1)
- SQL (2)
- JODConverter (1)
- xml (2)
- pdf2swf (1)
- flexpaper (1)
- android (2)
- jsp (5)
- mobile (1)
- weblogic (3)
- Tomcat (8)
- xss (1)
- db2 (2)
- webservice (1)
- spring (2)
- Gradle (1)
- linux (4)
- ldap (1)
- 软件工程 (1)
- 开发模型 (1)
- linux系统硬件配置查看方法 (1)
- cas (3)
- mysql (1)
- HAProxy (0)
- html (1)
- Web前端 (1)
最新评论
-
super_zou:
...
世界主要国家地区下拉菜单三级联动1 -
ldl_xz:
http://www.9958.pw/post/wenku 曾 ...
利用pdf2swf将PDF转换成SWF -
superlxw1234:
请教一下楼主,你们是如何解决使用MAgent时候,一台主Mem ...
memcached集群-magent -
头子:
中间那段关于“空隙”的解决方式很赞
HTML固定表头Table -
qiankun:
比较实用,好好学习了一下
Mybatis 在dao中获取分页的总记录数
发表评论
-
解决Oracle 11g在用EXP导出时,空表不能导出
2017-02-09 16:30 405一、问题原因: 1 ... -
ORA-12519: TNS:no appropriate service handler found 解决
2016-12-30 13:20 400# processes、sessions是扩大并发连接数, ... -
在Oracle中查询表的大小和表空间的大小
2016-12-29 11:18 549有两种含义的表大小。 ... -
Oracle定义DES加密解密及MD5加密函数示例
2016-12-16 11:22 621(1)DES加密函数 create or replac ... -
自定义字符串截取函数
2016-12-08 14:11 357CREATE OR REPLACE FUNCTION SBU ... -
通过v$sqlarea,v$sql查询最占用资源的查询
2016-03-15 15:47 450-----------------------v$sqlar ... -
Oracle的锁表与解锁
2015-12-11 13:58 1097Oracle的锁表与解锁 SELECT /*+ r ... -
oracle手动锁表
2015-12-11 12:19 824手工锁表: lock table tbl_t1 in ro ... -
oracle字符集设置
2015-03-16 14:40 536一、什么是Oracle字符集 Oracle字符集 ... -
行转列
2015-03-10 15:06 750select t.rank, t.Name from t_m ... -
ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值
2015-03-09 17:21 1731--- The error occurred in ibat ... -
更改数据库管理员密码
2014-11-05 13:24 1123更改数据库管理员密码; sqlplus /nol ... -
查询Oracle正在执行和执行过的SQL语句
2013-12-16 13:51 675---正在执行的select a.username, a.si ... -
Oracle 密码过期与锁定的设置
2012-09-26 10:28 1436查询默认的profile的密码过期参数的设置 ... -
Window 下 启动Oralce服务命令
2012-09-17 09:28 2815在window 下启动Oracle服务的命令 @ ... -
Oracle job 参数说明
2012-02-17 18:04 1387Job的参数: 一:时间间隔执行(每分钟,每天 ... -
行列转换
2012-01-16 13:49 1483数据库行列转换 SQLserver 2000 行列转换 ... -
oracle 随机函数
2012-01-14 19:51 1032Oracle随机函数—dbms_random ... -
Oracle 随机获取记录
2012-01-14 17:58 11831. Oracle 随机获取10条记录 ... -
Oracle创建用户、表空间、导入导出、...命令
2011-12-05 10:44 906Oracle创建用户、表空间、导入导出、...命令 / ...
相关推荐
需要注意的是,OLTP 系统应使用小但较多的回滚段,OLAP 系统/批处理系统应使用少量的大回滚段。在 OLTP/OLAP 混合型系统中,应专门设置一个或几个大的回滚段,平时设置为 OFFLINE,使用时通过使用 SET TRANSACTION ...
- **Undo太小**:Undo表空间的大小不足是触发ORA-01555的一个常见原因。如果Undo表空间不足以存储所有需要回滚的信息,或者保留的时间不够长,就可能导致前镜像被过早地清除。解决方案包括增加Undo表空间的大小,并...
具体来说,当Oracle尝试构建一个旧的事务快照时,如果发现回滚段中没有足够的信息来还原这个快照,就会触发ORA-01555错误。 ##### 解决方案 针对ORA-01555错误,可以采取以下几种策略来解决或预防: 1. **增加...
- `ORA-01650`:回退段空间不足。 - `ORA-01631`:表空间使用率过高。 - `Checkpoint Not Complete`:检查点未完成。 - `Snapshot too old`:快照过旧。 **后台进程跟踪文件**: - 路径同上。 - 示例文件名:...
说明: 如果值为TRUE, 即使源长度比目标长度 (SQL92 兼容) 更长, 也允许分配数据。 值范围: TRUE | FALSE 默认值: FALSE serializable: 说明: 确定查询是否获取表级的读取锁, 以防止在包含该查询的事务处理被提交...
当遇到ORA-1555错误(快照太旧),可能是因为回滚段空间不足或查询时间过长,可以通过增大回滚段保留时间或调整事务管理策略来避免。 5. **回退段分配**:不同实例间应使用独立的回退段,以便更有效地管理事务回滚...
19. **处理ORA-01555错误**:此错误提示快照过旧,通常通过增加回滚段大小解决,同时分析引发错误的SQL语句。 20. **$ORACLE_HOME与$ORACLE_BASE的区别**:$ORACLE_BASE是Oracle软件安装的基础目录,而$ORACLE_HOME...
TRUNCATE、ALTER TABLE DROP COLUMN 或 ALTER TABLE DROP PARTITION 等不支持使用 Flashback Table 进行回滚,因为这些操作属于数据定义语言(Data Definition Language, DDL),这类操作不会记录在事务回退段中,...