ORA-04016: "Sequence <Sequence Name> No Longer Exists" Error When Creating and Dropping a Sequence on RAC

(Doc ID 1300837.1)

Last updated on NOVEMBER 14, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
PL/SQL - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 27-Sep-2013***

Symptoms

Fast dropping and recreating of sequences like in code shown below on a RAC Instance will end up in

Error at line 1
ORA-04016: sequence <sequence name> no longer exists
ORA-06512: at line <line number>


Code

DECLARE
sStartId NUMBER := :STARTID;
sSelect LONG;
sPublisher VARCHAR (10) := :PUBLISHER;
iCount NUMBER := 0;
iMinAccountingMonthId NUMBER := 99999999;
iMaxAccountingMonthId NUMBER := 0;
sequence_object_found EXCEPTION;
PRAGMA EXCEPTION_INIT (sequence_object_found, -00955);
iTemp NUMBER;
p NUMBER := 0;

BEGIN
EXECUTE IMMEDIATE 'Alter session set cursor_sharing=force';

BEGIN
EXECUTE IMMEDIATE 'create sequence seq1 start with 1 increment by 1 nomaxvalue CACHE 1000';
EXCEPTION
WHEN sequence_object_found
THEN
EXECUTE IMMEDIATE 'drop sequence seq1';

EXECUTE IMMEDIATE 'create sequence seq1 start with 1 increment by 1 nomaxvalue CACHE 1000';
WHEN OTHERS
THEN
RAISE;
END;
for k IN 1..50 LOOP
EXECUTE IMMEDIATE 'select seq1.nextval from dual where 1=1'
INTO iTemp;
end loop;

EXECUTE IMMEDIATE 'drop sequence seq1';
END;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms