Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 17, 2018 - 3:15 pm UTC

Last updated: October 21, 2019 - 11:36 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Chris/Connor,

Could you please have a look ate below scenario:
Whenever we process orders in bulk lets say bunch of 1000 orders, out of which certain orders are failing in the MERGE statement with an error "ORA-12801: error signaled in parallel query server P023
ORA-08103: object no longer exists".

MERGE Statement looks like:
MERGE /* Q2 */ /*+ rowid(m) */ INTO smt_demo m
USING
(
SELECT /* Q1 */ /*+ leading(smt) */ DISTINCT
CAST(smt.rowid AS varchar2(128)) AS rid,
smt.zent_sk,
f.reg_id , f.cal_id FROM smt_demo smt, VW_FALL_ORDER f
WHERE
smt.order_id = '1234'
AND smt.order_oprn_typ IN ('U','I','D')
AND (smt.reg_id IS NULL OR smt.cal_id IS NULL )
AND smt.zent_sk = f.zent_sk
) target ON (m.rowid = CAST(target.rid AS urowid))
WHEN MATCHED THEN
UPDATE SET
m.reg_id = target.reg_id , m.cal_id = target.cal_id WHERE
m.order_id = '1234'
AND m.order_oprn_typ IN ('U','I','D')
AND m.zent_sk = target.zent_sk
AND (m.reg_id IS NULL OR m.cal_id IS NULL)

Details about objects involved in above statement:
Table smt_demo : is list partition table, for each orderid initally partiotion gets added in the table.
View VW_FALL_ORDER is view on range partition table of other scehma on same databse and we are using it via synonym.

I have verified the object status of table and the tables used in the view are valid.
When I run the statement manually it execute succesfully.
also after rerun of same statement for the failed orderids this statement gets executed without any issue.

Could you please share any thoughts and using that lead i can analyse this issue.

and Connor said...

I did a video on this recently that might explain things for you


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What about specifying partition

A reader, October 13, 2019 - 4:23 pm UTC

This video does not answer the very question.
What if my merge include the FOR PARTITION key?

Eg Merge into T partition for (myKey) tgt....
This will avoid ora-08103?
Connor McDonald
October 21, 2019 - 7:05 am UTC

I'm curious as to why you are joining on *rowid*. That is not necessarily immutable - rowid's can and do change, which can lead to your issue.


What about specifying partition

A reader, October 13, 2019 - 4:23 pm UTC

This video does not answer the very question.
What if my merge include the FOR PARTITION key?

Eg Merge into T partition for (myKey) tgt....
This will avoid ora-08103?

Not relevant to the question

A reader, October 21, 2019 - 8:46 am UTC

"
I'm curious as to why you are joining on *rowid*"


the is no such word in my question.
When a merge start to do some update and another session say try to delete some partitions that NOT concerning the one of the Merge then we can encounter such error. My question is could we avoid it by SPECIFYING the FOR PARTITION ?


Connor McDonald
October 21, 2019 - 11:36 am UTC

and another session say try to delete some partitions

Well...isn't it nice that you decide to suddenly share THAT piece of information.

If *any* part of your merge processing touches a partition that has been relocated/dropped/etc, then you run the risk.

This could be the *target* or the *source*

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library