ORA-14403 cursor invalidation detected after getting DML partition lock - lots of retries
Date: Mon, 28 Nov 2022 12:14:51 +0000
Message-ID: <CAPMSPxOTBx-u=xggmOBPyCxPZ3nog8OKDH0nWPm-qUG-xwmL8w_at_mail.gmail.com>
Hi
Oracle Database EE 11.2.0.4.0
This database started displaying odd behaviour with SQL insert statements driven by a select statement, inserting into a partitioned table, being run many times. Eventually I dscovered ORA-14403. As far as I can tell the "re-try" is caused by cursor invalidation, but I do not know why the cursor is being marked as invalid. There is no DDL taking place against the partitoned table or statistics gathering going on. The insert statement is run and the cursor starts off as being"VALID" and then at some un-predictable time it gets marked as"INVALID_UNAUTH", once the session is ready to insert the ORA-14403 error gets raised and handled internally. Clearing the cursor out of the shared_pool or flushing the shared_pool completely has no impact.
The best reference I have found (outside of MOS) is this blog post by
Randolph Geist :-
https://oracle-randolf.blogspot.com/2016/01/dml-operations-on-partitioned-tables.html
My situation is not exactly the same, since I have confimed no DDL is taking place and no stats are being gathered.
Any suggestions as to why this is happening?
Regards
Pete
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 28 2022 - 13:14:51 CET