How to update records without invalidating cursor oracle
The DBA_OBJECTS view Almost every object in an Oracle database has an entry in DBA_OBJECTS view.
We query this view when we want to find out details about an object such as whether a given object is a table or index, when it was created, when a DDL statement last modified it, etc.
Caching has been a feature of Oracle for as long as most of us can remember.
A PL/SQL block is made up of three sections (declaration, executable and exception), of which only the executable section is mandatory.
The optional declaration section allows variables, types, procedures and functions do be defined for use within the block.
I haven't explicitly tested UPDATE and DELETE statements, but the assumption is that they are affected, too.
The behaviour is documented in the following note on MOS: "Insert Statement On Partitioned Tables Is RE-Started After Invalidation (Doc ID 1462003.1)" which links to Bug "14102209 : INSERT STATEMENT' IS RESTARTING BY ITSELF AFTER INVALIDATION" where you can also find some more comments on this behaviour.
Note that this can happen multiple times - actually it's possible to end up in a kind of infinite loop when this happens, leading to statements that can run for very, very long (I've seen statements on Production environments executing for several days although a single execution would only take minutes).