This discussion is archived
1 Reply Latest reply: Mar 30, 2012 6:02 AM by empee RSS

ORA-04016 - Sequence no longer exists

827059 Newbie
Currently Being Moderated
Hi,

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 11.2.0.2 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?

Thanks
  • 1. Re: ORA-04016 - Sequence no longer exists
    empee Oracle ACE
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points