drop user cascade 导致row cache lock

Applies to:

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

Symptoms

• In PDB of RAC of 2 nodes, drop user cascade command hung on 'DLM cross inst call completion'.
   

      Current Wait Stack:
       0: waiting for 'DLM cross inst call completion'
          caller instance number=0x1, cluster incarnation number=0x8, request identifier=0x22fffba
          wait_id=72777 seq_num=8430 snap_id=3
          wait times: snap=182 min 29 sec, exc=201 min 52 sec, total=201 min 52 sec
          wait times: max=infinite, heur=201 min 52 sec
          wait counts: calls=0 os=0
          in_wait=1 iflags=0x15a2

• Systemstate dump trace shows the session was requesting for dc_realtime_colst.

           row cache enqueue: count=1 session=0x522b5a970 object=0x3981d72b8, request=X
            flag=02 -/TRC/-/-/-/-/-/- savepoint=0x3bfa77
            type=MULTI-INSTANCE instance locked=F handle=0x772920598
            row cache parent object: addr=0x3981d72b8 cid=62(dc_realtime_colst) conid=3 conuid=1025773164 <<<<<<<<<<<<<dc_realtime_colst
            hash=9bee6085 typ=61 transaction=(nil) flags=00000000 inc=1, pdbinc=6
            own=0x3981d7388[0x3981d7388,0x3981d7388] wat=0x3981d7398[0x772920548,0x772920548] mode=N req=X

• Target user owns a large partition table with hundreds of partition and more than 10 thousand subpartitions.
• Call stack shows the process was executing cross instance call:
  • <-kjci_processcrq()+224<-kjci_wait()+607<-kqlmClusterMessageEnd()+141<-kqreqd()+2572<-kssrch()+339<-ktcrsp1_new()+445<-ktcrsp1()+801<-opiosq0()+5342<-opiodr()+1202<-rpidrus()+198<-skgmstack()+65<-rpidru()+132<-rpiswu2()+541<-rpidrv()+1248<-rpisplu_internal()+474<-kzddao_flags()+4739<-kzdukl()+18187<-kzudrp()+2479<-opiexe()+27823<-opiosq0()+4599<-kpooprx()+387<-kpoal8()+830<-opiodr()+1202<-ttcpip()+1222<-opitsk()+1895<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245

Cause

 It appears that the session was executing the cross instance call for the invalidation of the huge number of row cache entries which are gotten during "drop user cascade".
This problem is being investigated by internal Bug 31208287 and Bug 31038220.

Solution

1. Bug 31038220 & 31208287 are both fixed in 19.9.0.0.201020 (Oct 2020) Database Release Update(DB RU)

2. Before the completion of above Bug investigation, please apply following workaround.
 

drop table <TABLE_NAME> purge;
... ...
drop user <USERNAME>;