- 浏览: 643093 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
HkEndless:
不好意思,请问这确定是回调机制吗。你的例子中只是将接口的实现类 ...
Spring CallBack回调机制介绍 -
hanmiao:
写的真乱啊,完全不知所云...
Java如何调用可执行文件和批处理命令 -
junia_1:
junia_1 写道 shock: ...
为什么要使用EJB -
junia_1:
shock:
为什么要使用EJB -
coollifer:
不错
SQL Server数据导入到Oracle中的方法
假设有张表,叫table1,里面有5000万行数据,假设预计全表扫描1次需要1个小时,我们从过程来看:
1、在1点钟,有个用户A发出了select * from
table1;此时不管将来table1怎么变化,正确的结果应该是用户A会看到在1点钟这个时刻table1的内容。这个是没有疑问的。
2、在1点30分,有个用户B执行了update命令,更新了table1表中的第4000万行的这条记录,这时,用户A的全表扫描还没有到达第4000
万条。毫无疑问,这个时候,第4000万行的这条记录是被写到了回滚段里去了的,我假设是回滚段RBS1,如果用户A的全表扫描到达了第4000万行,是
应该会正确的从回滚段RBS1中读取出1点钟时刻的内容的。(未更新前的数据)
3、这时,用户B将他刚才做的操作commit了,但是这时,系统仍然可以给用户A提供正确的数据,因为那第4000万行记录的内容仍然还在回滚段
RBS1里,系统可以根据SCN来到回滚段里找到正确的数据,但是大家注意到,这时记录在RBS1里的第4000万行记录已经发生了一点重大的改变:就是
这个第4000万行的在回滚段RBS1里的数据有可能随时被覆盖掉,因为这条记录已经被提交了!
4、由于用户A的查询时间漫长,而业务在一直不断的进行,RBS1回滚段在被多个不同的tracnsaction使用着,这个回滚段里的extent循环到了第4000万行数据所在的extent,由于这条记录已经被标记提交了,所以这个extent是可以被其他transaction覆盖掉的!
5、到了1点40分,用户A的查询终于到了第4000万行,而这时已经出现了第4条说的情况,需要到回滚段RBS1去找数据,但是已经被覆盖掉了,于是01555就出现了。
ORA-01555 Explanation
There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a ‘read consistent‘ image. These are :o The rollback information itself is overwritten so that Oracle is unable to rollback the (committed) transaction entries to attain a sufficiently old enough version of the block.o The transaction slot in the rollback segment‘s transaction table (stored in the rollback segment‘s header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.Both of these situations are discussed below with the series of steps that cause the ORA-01555. In the steps, reference is made to ‘QENV‘. ‘QENV‘ is short for ‘Query Environment‘, which can be thought of as the environment that existed when a query is first started and to which Oracle is trying to attain a read consistent image. Associated with this environment is the SCN (System Change Number) at that time and hence, QENV 50 is the query environment with SCN 50.CASE 1 - ROLLBACK OVERWRITTENThis breaks down into two cases: another session overwriting the rollback that the current session requires or the case where the current sessionoverwrites the rollback information that it requires. The latter is discussed in this article because this is usually the harder one to understand.Steps:1. Session 1 starts query at time T1 and QENV 502. Session 1 selects block B1 during this query3. Session 1 updates the block at SCN 514. Session 1 does some other work that generates rollback information.5. Session 1 commits the changes made in steps ‘3‘ and ‘4‘.
(Now other transactions are free to overwrite this rollback information)6. Session 1 revisits the same block B1 (perhaps for a different row).Now, Oracle can see from the block‘s header that it has been changed and it is later than the required QENV (which was 50). Therefore we need to get an image of the block as of this QENV.If an old enough version of the block can be found in the buffer cache then we will use this, otherwise we need to rollback the current block to generate another version of the block as at the required QENV.It is under this condition that Oracle may not be able to get the required rollback information because Session 1‘s changes have generated rollback information that has overwritten it and returns the ORA-1555 error.CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN1. Session 1 starts query at time T1 and QENV 502. Session 1 selects block B1 during this query3. Session 1 updates the block at SCN 514. Session 1 commits the changes
(Now other transactions are free to overwrite this rollback information)5. A session (Session 1, another session or a number of other sessions) then use the same rollback segment for a series of committed transactions.These transactions each consume a slot in the rollback segment transaction table such that it eventually wraps around (the slots are written to in a circular fashion) and overwrites all the slots. Note that Oracle is free to reuse these slots since all transactions are committed.6. Session 1‘s query then visits a block that has been changed since the initial QENV was established. Oracle therefore needs to derive(获得) an image of the block as at that point in time.Next Oracle attempts to lookup the rollback segment header‘s transaction slot pointed to by the top of the data block. It then realises that this has been overwritten and attempts to rollback the changes made to the rollback segment header to get the original transaction slot entry.If it cannot rollback the rollback segment transaction table sufficiently it will return ORA-1555 since Oracle can no longer derive the required version of the data block.It is also possible to encounter a variant of the transaction slot being overwritten when using block cleanout. This is briefly described below :Session 1 starts a query at QENV 50. After this another process updates the blocks that Session 1 will require. When Session 1 encounters these blocks it determines that the blocks have changed and have not yet been cleaned out (via delayed block cleanout). Session 1 must determine whetherthe rows in the block existed at QENV 50, were subsequently changed,In order to do this, Oracle must look at the relevant rollback segment transaction table slot to determine the committed SCN. If this SCN is after the QENV then Oracle must try to construct an older version of the block and if it is before then the block just needs clean out to be good enough forthe QENV.If the transaction(处理) slot has been overwritten and the transaction table cannot be rolled back to a sufficiently old enough version then Oracle cannot derive the block image and will return ORA-1555.(Note: Normally Oracle can use an algorithm for determining a block‘s SCN during block cleanout even when the rollback segment slot has been overwritten.But in this case Oracle cannot guarantee that the version of the block has not changed since the start of the query).
(Same reason as 1).4.Add additional(附加) rollback segments. This will allow the updates etc. to be spread across more rollback segments thereby reducing the chances of overwriting required rollback information.5.If fetching across commits, the code can be changed so that this is not done.6.Ensure that the outer select does not revisit the same block at different times during the processing. This can be achieved by :- Using a full table scan rather than an index lookup
- Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially visit these data blocks.CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN1. Use any of the methods outlined above except for ‘6‘. This will allow transactions to spread their work across multiple rollback segments therefore reducing the likelihood or rollback segment transaction table slots being consumed.2. If it is suspected that the block cleanout variant is the cause, then force block cleanout to occur prior to the transaction that returns the ORA-1555. This can be achieved by issuing the following in SQL*Plus, SQL*DBA or Server Manager :alter session set optimizer_goal = rule;
select count(*) from table_name;If indexes are being accessed then the problem may be an index block and clean out can be forced by ensuring that all the index is traversed. Eg, if the index is on a numeric column with a minimum value of 25 then the following query will force cleanout of the index :select index_column from table_name where index_column > 24;Examples
~~~~~~~~
Listed below are some PL/SQL examples that can be used to illustrate the ORA-1555 cases given above. Before these PL/SQL examples will return this error the database must be configured as follows :o Use a small buffer cache (db_block_buffers).REASON: You do not want the session executing the script to be able to find old versions of the block in the buffer cache which can be used to satisfy a block visit without requiring the rollback information.o Use one rollback segment other than SYSTEM.REASON: You need to ensure that the work being done is generating rollback information that will overwrite the rollback information required.o Ensure that the rollback segment is small.REASON: See the reason for using one rollback segment.ROLLBACK OVERWRITTENrem * 1555_a.sql -
rem * Example of getting ora-1555 "Snapshot too old" by
rem * session overwriting the rollback information required
rem * by the same session.drop table bigemp;
create table bigemp (a number, b varchar2(30), done char(1));drop table dummy1;
create table dummy1 (a varchar2(200));rem * Populate the example tables.
begin
for i in 1..4000 loop
insert into bigemp values (mod(i,20), to_char(i), ‘N‘);
if mod(i,100) = 0 then
insert into dummy1 values (‘ssssssssssss‘);
commit;
end if;
end loop;
commit;
end;
/rem * Ensure that table is ‘cleaned out‘.
select count(*) from bigemp;declare
-- Must use a predicate so that we revisit a changed block at a different
-- time.-- If another tx is updating the table then we may not need the predicate
cursor c1 is select rowid, bigemp.* from bigemp where a < 20;begin
for c1rec in c1 loopupdate dummy1 set a = ‘aaaaaaaa‘;
update dummy1 set a = ‘bbbbbbbb‘;
update dummy1 set a = ‘cccccccc‘;
update bigemp set done=‘Y‘ where c1rec.rowid = rowid;
commit;
end loop;
end;
/ROLLBACK TRANSACTION SLOT OVERWRITTENrem * 1555_b.sql - Example of getting ora-1555 "Snapshot too old" by
rem *overwriting the transaction slot in the rollback
rem *segment header. This just uses one session.drop table bigemp;
create table bigemp (a number, b varchar2(30), done char(1));rem * Populate demo table.
begin
for i in 1..200 loop
insert into bigemp values (mod(i,20), to_char(i), ‘N‘);
if mod(i,100) = 0 then
commit;
end if;
end loop;
commit;
end;
/drop table mydual;
create table mydual (a number);
insert into mydual values (1);
commit;rem * Cleanout demo table.
select count(*) from bigemp;declarecursor c1 is select * from bigemp;begin-- The following update is required to illustrate the problem if block
-- cleanout has been done on ‘bigemp‘. If the cleanout (above) is commented
-- out then the update and commit statements can be commented and the
-- script will fail with ORA-1555 for the block cleanout variant.
update bigemp set b = ‘aaaaa‘;
commit;for c1rec in c1 loop
for i in 1..20 loop
update mydual set a=a;
commit;
end loop;
end loop;
end;
/Special Cases
~~~~~~~~~~~~~
There are other special cases that may result in an ORA-01555. These are given below but are rare and so not discussed in this article :o Trusted Oracle can return this if configured in OS MAC mode. Decreasing LOG_CHECKPOINT_INTERVAL on the secondary database may overcome the problem.o If a query visits a data block that has been changed by using the Oracle discrete transaction facility then it will return ORA-01555.o It is feasible that a rollback segment created with the OPTIMAL clause maycause a query to return ORA-01555 if it has shrunk during the life of the query causing rollback segment information required to generate consistent read versions of blocks to be lost.Summary
~~~~~~~
This article has discussed the reasons behind the error ORA-01555 "Snapshot too old", has provided a list of possible methods to avoid the error when it is encountered, and has provided simple PL/SQL scripts that illustrate the cases discussed.
发表评论
-
ORA-14551: 无法在查询中执行 DML 操作
2013-11-30 13:45 1293最近在调试一个带DML操作的函数时,一直不成功,在PL/SQ ... -
Oracle Recursive Calls 说明
2013-04-09 23:11 1447一. Recursive Calls 说明 在执行计划 ... -
consistent gets db block gets
2013-04-09 19:58 1398consistent gets :consistent_ge ... -
SQL Server数据导入到Oracle中的方法
2012-07-17 17:09 1551在我们使用SQL Server数据库的过程中,有时需要将SQL ... -
更改ORACLE归档路径及归档模式
2012-07-16 18:23 1817在ORACLE10g和11g版本,ORAC ... -
disable/enable validate/novalidate 的区别
2012-01-08 11:41 1153启用约束: enable( validate) :启用约束,创 ... -
linux用dd测试磁盘速度
2012-01-07 21:58 963首先要了解两个特殊的设备: /dev/null:回收站、 ... -
在数据分布严重不均的列上使用绑定变量容易错过更好的执行计划
2012-01-07 20:49 971在数据分布严重不均的列上使用绑定变量容易错过更好的执行计划,原 ... -
Oracle hash join
2012-01-07 17:00 900hash join是oracle里面一个非常强悍的功能 ... -
恢复被rm意外删除数据文件
2012-01-05 12:30 1195一.模拟数据文件删除 [oracle ... -
oracle 块延迟清除(delayed block cleanout)
2012-01-04 22:47 1314为了保证事务的回退和满足多用户的 CR , orac ... -
Oracle数据库SCN号详解
2012-01-04 19:25 1504Oracle数据库SCN号详解: 系统检查点scn(v$da ... -
oracle常见问题与解答
2012-01-03 20:22 16271.对于sql,有几种方法查看执行计划,每种方法有什么区别,对 ... -
Oracle虚拟私有数据库(VPD)概述及简单举例
2011-12-23 12:35 1467Oracle虚拟私有数据库(VPD)概述及简单举例 1、Ora ... -
alter table move跟shrink space的区别
2011-12-17 15:02 1217都知道alter table move 或shrink spa ... -
How to dump Oracle Data Block?
2011-12-16 15:22 960Often while doing instance tuni ... -
oracle索引的5种使用模式
2011-12-14 21:19 1046索引的使用对数据库的性能有巨大的影响。 共有五类不同的使用模式 ... -
HP Unix中的dba MLOCK
2011-12-14 19:14 2181最近在HP平台上遇到两次跟dba MLOCK权限相关的错误: ... -
NESTED LOOP、HASH JOIN、SORT MERGE JOIN
2011-12-13 23:18 1340表连接方式及使用场合 ... -
Oracle用户权限
2011-12-12 19:48 1451系统权限: 1、使用GRANT语句向用户赋予系统权限: ...
相关推荐
oracle expdp导出blob字段遇到ora-01555报错的解决方案
ORA-01555错误浅析,分析的很好
ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法ora-00604 错误 解决 方法
Oracle 11gr2连Oracle 19c 报ORA-28040 ORA-01017解决方法
因此,在数据库的日常维护工作中,如果只是...2. 导致ORA-01555错误的主要原因有哪些?1) undo太小比较直观的解决方法是DBA告诉数据库应用最长的查询需要多长时间,并把UNDO_RETENTION设为这个值,同时相应增大undo表
Drop goldengate用户时,报ORA-00604 ORA-20782 ORA-06512错误
使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查。使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查 使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查
oracle数据库ora-01152和ora-01110的解决办法
离线误删空间文件导致的ORA-01033及ORA-01145问题的解决办法,在解决ORA-01033的过程中,又出现ORA-01145 * 第 1 行出现错误: ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机 接着的解决步骤
ORA-12541 TNSno listener 的解决方案 ORA-12541 TNSno listener 的解决方案
用oracle数据库新建连接时遇到ora-12505,此问题解决后又出现ora-12519错误,郁闷的半天,经过一番折腾问题解决,下面小编把我的两种解决方案分享给大家,仅供参考。 解决方案一: 今天工作时在新建连接的时候遇到...
oracle网络配置(listener_ora-sqlnet_ora-tnsnames_ora).mht
oracle启动失败,ORA-00702报错,windows,linux系统下解决办法
创建物化视图ORA-12014错误解决方法 创建物化视图ORA-12014错误解决方法
关于WIN10系统使用oracle instant client 时候提示ORA-01019错误的解决方案,本方案是配置好环境变量后依然提示ORA-01019错误的解决方案,内附本人制作测试的全过程说明
ORACLE ORA-00132 ORA-00214
在运行查询SELECT * FROM V$SESSION 会出现ORA-29275:部分多字节字符的错误,这是什么原因开始我不得其解,网上也没有介绍什么好办法。本文给出答案。
客户端进行连接的时候,系统不定期出现ora-12520,ora-12516的连接问题, 问题解决方案建议: 1、增加process和session的连接数。 2、检查连接的应用,是不是有没有释放的连接。 3、将修改参数local_listener中的vip为...
错误描述:oracle远程连接服务器出现 ORA-12170 TNS:连接超时 错误检查:有很多是oracle自身安装的问题,但是我这里服务器配置正常,监听正常,服务正常,远程可以ping通服务器。 这里主要是防火墙问题,解决办法: ...