1 Reply Latest reply: Mar 30, 2012 8:02 AM by empee RSS

    ORA-04016 - Sequence no longer exists

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