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

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
>

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