RE: alter table move partition - row cache lock

I’m confused. While you can configure a service to only connect to a specific instance (and possibly an alternative if the preferred instance is down), I don’t remember that in and of itself preventing a parallel operation from using multiple instances. I believe that is only controlled by setting parallel_force_local for at least the current session.  

Someone correct me if I’m wrong.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea Sent: Friday, February 26, 2021 9:27 AM
To: Mark W. Farnham
Cc: ORACLE-L
Subject: Re: alter table move partition - row cache lock  

Sorry I missed to provide full details. Yes this is RAC. Cache_id is 2 (dc_segments). Coordinator and query slaves (8) are all running on the same instance (due to service configured for a single instance)  

În vin., 26 feb. 2021 la 16:22, Mark W. Farnham <mwf_at_rsiz.com> a scris:

Is this non-RAC?  

Is this RAC, but force local instance for parallel?  

Is this RAC non-local?  

How many query “slaves?”  

I believe that hashes the useful research options of why you are seeing a non-propagated lock held by the co-ordinator.    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea Sent: Friday, February 26, 2021 7:59 AM
To: ORACLE-L (oracle-l_at_freelists.org) Subject: alter table move partition - row cache lock  

Hello,  

DB Version 12.0.1.2  

I noticed a strange situation and I`m curios if someone faced a similar situation or have any clue about the reason:  

While executing a partition move:

ALTER TABLE t move PARTITION p TABLESPACE t ROW store COMPRESS advanced lob l store AS (COMPRESS medium TABLESPACE tl) UPDATE INDEXES PARALLEL 8;  
-> one query slave remain stuck into wait "row cache lock" (p2 = 0, p3 =3)

-> the blocker is actual query coordinator who is idle all this time
 

Much appreciated,

Laurentiu.

--
http://www.freelists.org/webpage/oracle-l