Meger语句在特定情况下,会抛出“ORA-08006 specified row no longer exists” exception以下SQL,可以使exception再现。
CREATE TABLE SOURCE_test
AS
SELECT object_id, object_name, object_type
FROM all_objects
WHERE OBJECT_name NOT LIKE '%$%' AND object_name NOT LIKE '%#%';
INSERT INTO source_test
SELECT object_id, object_name, object_type
FROM source_test;
INSERT一句,使source_test中,同一个object_id和object_name,有两条数据。
CREATE TABLE target_test
AS
SELECT object_id, object_name, object_type
FROM all_objects
WHERE object_id < 5000
AND OBJECT_name NOT LIKE '%$%' AND object_name NOT LIKE '%#%';
INSERT INTO target_test
SELECT object_id, object_name, object_type
FROM target_test;MERGE INTO target_test t
USING source_test s
ON (s.object_id = t.object_id AND s.object_name = t.object_name)
when matched then update set t.object_type = s.object_type
DELETE WHERE (s.object_id = t.object_id AND s.object_name = t.object_name);oracle的文档中,有关于ORA-08006这种情况的说明吗?
CREATE TABLE SOURCE_test
AS
SELECT object_id, object_name, object_type
FROM all_objects
WHERE OBJECT_name NOT LIKE '%$%' AND object_name NOT LIKE '%#%';
INSERT INTO source_test
SELECT object_id, object_name, object_type
FROM source_test;
INSERT一句,使source_test中,同一个object_id和object_name,有两条数据。
CREATE TABLE target_test
AS
SELECT object_id, object_name, object_type
FROM all_objects
WHERE object_id < 5000
AND OBJECT_name NOT LIKE '%$%' AND object_name NOT LIKE '%#%';
INSERT INTO target_test
SELECT object_id, object_name, object_type
FROM target_test;MERGE INTO target_test t
USING source_test s
ON (s.object_id = t.object_id AND s.object_name = t.object_name)
when matched then update set t.object_type = s.object_type
DELETE WHERE (s.object_id = t.object_id AND s.object_name = t.object_name);oracle的文档中,有关于ORA-08006这种情况的说明吗?
解决方案 »
- 请教一个问题,,关于 10g 连接的问题,菜鸟 呵呵
- 查询语句中带有BLOB字段的速度很慢
- 如何在PLSQL中测试我自己写的存储过程???
- UNDO表空间和XDB表空间异常
- 我在Imp数据的时候,当跑到准备启用约束条件的时候,就挂死了,清高手指点!
- 请问关于数据库主键的一些问题
- 高分求解,请高手帮忙!GROUP BY分组的问题
- 初始化提供程序时发生错误,测试连接失败.发生了一个oracle错误,但无法从oracle中检索错误信息?
- 谁可以向我推荐一个数据库服务器(小型机)的配置?
- ORA-12541: TNS: 没有监听器 Error
- 用sqlplus怎样把数据导到excel表格中去?
- 为什么使用SQL*Plus Worksheet执行带有替换变量的SQL语句会出错
Cause: the row has been deleted by another user since the operation began
Action: re-try the operation
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production所以我猜测oracle认为不是bug。
1.两句insert都不执行
2.执行source_test的insert,不执行另外一个
3.执行target_test的insert,不执行另外一个除了8006,还有可能是30926之类的错误。
加上 when not matched thenMERGE INTO target_test t
USING source_test s
ON (s.object_id = t.object_id AND s.object_name = t.object_name)
when matched then update set t.object_type = s.object_type
when not matched then DELETE WHERE (s.object_id = t.object_id AND s.object_name = t.object_name);