My Oracle Support Banner

Insert Over Dblink Get Ora-00907 (Doc ID 444002.1)

Last updated on JUNE 05, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.3 [Release 9.2 to 10.2]
Information in this document applies to any platform.

Symptoms

When trying to insert into a remote table using local tables, the insert fails with ORA-907.
The problem happened only when you gather statistics on the local tables.
If no statistics are gathered, then the insert runs fine.

If you are getting ORA-907 while inserting into local table, then you are likely to be hitting the same problem.

The issue is also reproducible using loopback db links.

For example:

create user <user1> identified by <password>;
create user <user2> iidentified by<password>;
grant dba to <user1>;
grant dba to <user2>;
conn <user1>/<password>
create table t(c2 date);
conn <user2>/<password> 
--Assuming inst1 is the connect identifier---
create public database link linkf connect to <user1> identified by <password> using 'inst1';
create public synonym s for t@linkf;
conn <user1>/<password>
create table t1( c1 number , c2 date, c3 number);
create table t2(kza_id number(10) );
insert into s(c2) select c2 from t1 fl0,t2
where fl0.rowid = (select max(fl1.rowid) from t1 fl1,t2
where c1 = t2.kza_id
and fl1.c2 = (select max(fl2.c2) from t1 fl2,t2 kat2
where fl2.c3 = fl1.c3
and fl2.c1 = kat2.kza_id )
)
/
--Now Gather statistics
exec dbms_stats.gather_table_stats(null,'t1');
exec dbms_stats.gather_table_stats(null,'t2');
And rerun the same insert command and it shall fail with ora-907

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.