In the below scenario
1. All the sequences are dropped
2. When a process requires a value from a sequence and the sequence is not available, it is created and then a sequence.nextval is executed.
This statement is returning a ORA-04016 - Sequence <XXX> no longer exists.
Based on the following Metalink 1300837.1, we tried putting a sleep(5) between the sequence creation and fetch. The problem still occurs intermittently.
DB Version is 188.8.131.52 on Exadata with 6 nodes.
Even after an extensive search on the web, I could not find anything.
Is there any workaround for this or is there some DB patch to be applied?
Have you tried sleeping for longer?
Alternative approaches that spring to mind are:
1) Ensure that the statements that create the sequence and those that subsequently use it are executed on the same node when executed in close succession.
2) Trap the error and retry in your code.
I'd be inclined to favour suggestion 1 if possible in your environment.
Is your code in PL/SQL?