Re: ORA-14403 cursor invalidation detected after getting DML partition lock - lots of retries

Hello Jonathan,
You are correct, it is an interval partitioned table, partitioned on a timestamp column, which I should have said. However the partitions are created yearly, so most of the time inserts do not create new partitions (which I know from reading your blog is a DML operation).

Originally I thought this was a SQL tuning issue (so I could scatch my CTD itch) , then I thought it was something to do with ORA-1551 but then I also came across ORA-14403 and setting event 14403 proved it (from MOS 2057107.1).

This system has a lot of "INSERT ... SELECT..." operations and Oracle seems to be picking on a few and one in partitcular to invalidate the cursor. I have been able to reproduce this in development and I only got the INSERT to work after I drastically reduced the database workload. In production I have seen cursors invalidated and then after some time (which can be anything from 8 hours to 4 days), the INSERT completes.

This is a mature system, obviously no longer on a supported Oracle DB release, which started off life as release 11.1.0.7. What I also don't know is how long this has been going on for.

Regards
Pete

On Mon, 28 Nov 2022 at 12:52, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Just a guess that might fit your description: is it interval
> partitioning? If so an insert that required a new partition to exist would
> not show any DDL but might trigger the 14403.
>
> Regards
> Jonathan Lewis
>
>
> On Mon, 28 Nov 2022 at 12:15, Peter Hitchman <pjhoraclel_at_gmail.com> wrote:
>
>> 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
>>
>

-- 
Regards

Pete

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