OraFAQ Forum: SQL & PL/SQL » ORA-30926: unable to get a stable set of rows in the source tables

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30926: unable to get a stable set of rows in the source tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-30926: unable to get a stable set of rows in the source tables [message #239187]

Mon, 21 May 2007 12:54

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Hi,
i have two databases and two identical tables in that.
we usually daily insert from one database table to another.for that we wrote merge statement.that statment was running successfully for the last 1 month,but today when i checked there was error in log file..
this one

ORA-30926: unable to get a stable set of rows in the source tables

Any suggestions

this is mine merge statement

MERGE INTO XXXX t1
USING yyyyy@abc t2
ON (t2.Request_time = t1. Request_time)
WHEN MATCHED
THEN UPDATE
SET t1.server = t2.server,
T1.service = t2.service,
T1.client = T2.service,
T1.ts_transaction=t2.ts_transaction,
T1.elapsed_time =t2.elapsed_time
WHEN NOT MATCHED THEN
INSERT (t1.request_time, t1.server, t1.service, t1.client,
t1.ts_transaction, t1.elapsed_time)
VALUES (t2.request_time, t2.server, t2.service, t2.client,
t2.ts_transaction, t2.elapsed_time);

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239190 is a reply to message #239187]

Mon, 21 May 2007 13:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It could be a bug, but since you neglected to post Oracle version to 4 decimal places, nobody but you will know.

Note:4549673.8

[Updated on: Mon, 21 May 2007 13:06] by Moderator

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239191 is a reply to message #239187]

Mon, 21 May 2007 13:06

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Read and apply How to format your posts
2/ Read and apply How to get a quick answer to your question: TIPS AND TRICKS
3/

ora-30926: unable to get a stable set of rows in the source tables
 *Cause:  A stable set of rows could not be got because of large dml
          activity or a non-deterministic where clause.
 *Action: Remove any non-deterministic where clauses and reissue the dml.

Check your ON clause else you have a too large DML.

Regards
Michel

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239193 is a reply to message #239190]

Mon, 21 May 2007 13:06

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

oracle version is 10.2.0.3.0
and os is solaris 10 64 bit

but it was working 30 days...because it was run by cron daily..

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239196 is a reply to message #239193]

Mon, 21 May 2007 13:16

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

but it was working 30 days...because it was run by cron daily..

What does this mean? /forum/fa/1580/0/

/forum/fa/1580/0/

Regards
Michel

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239198 is a reply to message #239196]

Mon, 21 May 2007 13:19

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

This query is scheduled with crotab, and runs automatically daily after peak hours.For the last one month it merged the rows successfully but on weekened it produced this error.

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239199 is a reply to message #239198]

Mon, 21 May 2007 13:33

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And during week-end there is no other concurrent batches?

Regards
Michel

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239201 is a reply to message #239199]

Mon, 21 May 2007 13:42

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

All other ran successfully..but this job failed for the first time since its running(round about 35 days).

One more amazing thing.its merged the rows n than it trown that error,because mine both tables are at same count.

[Updated on: Mon, 21 May 2007 13:48]

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239203 is a reply to message #239201]

Mon, 21 May 2007 13:58

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And now it fails every time or from time to time?

Regards
Michel

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239206 is a reply to message #239203]

Mon, 21 May 2007 14:05

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

EVERY TIME.
wheater i run insert statement procedure for insert/update or merge statement it fails with the same error but

when i run this command on both table count shows equal in both

select count(*) from abc

10
and

select count(*) from abc@abc
10

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239207 is a reply to message #239206]

Mon, 21 May 2007 14:13

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Seems you have to contact support on this one.

Regards
Michel

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239211 is a reply to message #239207]

Mon, 21 May 2007 14:24

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Thanks.
Yea i'm thinking too.

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239216 is a reply to message #239211]

Mon, 21 May 2007 15:05

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

PROBLEM FIXED by using inline View.

Thanks every buddy;) Laughing

Laughing

[Updated on: Mon, 21 May 2007 15:09]

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239324 is a reply to message #239216]

Tue, 22 May 2007 01:00

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the solution.

Regards
Michel

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239526 is a reply to message #239324]

Tue, 22 May 2007 10:28

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

solution is:

MERGE into xxxxx T1
Using (select request_time, server, service, client,
                ts_transaction, elapsed_time from yyyy@dev01_ds) T2
on  (T1.Request_time = T2.Request_time )
WHEN MATCHED
  THEN UPDATE
       SET t1. elapsed_time = t2. elapsed_time 
When not matched then
Insert 
(server, service, client,
                ts_transaction, elapsed_time)
values
( t2.server, t2.service, t2.client,
                T2.ts_transaction, t2.elapsed_time);

[Updated on: Tue, 22 May 2007 18:31]

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239539 is a reply to message #239526]

Tue, 22 May 2007 10:54

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Nice to post the solution.
Next time:
format, FORMAT,

FORMAT

Regards
Michel

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #239541 is a reply to message #239539]

Tue, 22 May 2007 10:55

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

aww okay.

Report message to a moderator

Previous Topic:	List of modified tables Modifications
Next Topic:	CAN I PASS A TABLE NAME AS A VARIABLE IN THE FROM CLAUSE?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 22 20:15:58 CDT 2025