Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30926: unable to get a stable set of rows in the source tables
ORA-30926: unable to get a stable set of rows in the source tables [message #239187] Mon, 21 May 2007 12:54 Go to next message
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);
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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..
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but it was working 30 days...because it was run by cron daily..

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

Regards
Michel
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And during week-end there is no other concurrent batches?

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And now it fails every time or from time to time?

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Seems you have to contact support on this one.

Regards
Michel
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 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Thanks.
Yea i'm thinking too.
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 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
PROBLEM FIXED by using inline View.


Thanks every buddy;) 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the solution.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice to post the solution.
Next time:
format, FORMAT,
FORMAT

Regards
Michel
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 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
aww okay.
Previous Topic: List of modified tables Modifications
Next Topic: CAN I PASS A TABLE NAME AS A VARIABLE IN THE FROM CLAUSE?
Goto Forum:
  


Current Time: Sun Dec 11 04:23:29 CST 2016

Total time taken to generate the page: 0.07493 seconds