ORA-02289: sequence does not exist

The ORA-02289: sequence does not exist error occurs when the sequence does not exist, the sequence name is wrong, or the user does not have the necessary permissions to execute it. The sequence may not be added to or removed from the database. The user is not granted the necessary permission to execute the sequence, or the user’s authorization to execute the sequence is revoked. Check that the sequence name is correctly spelt and that the user has the necessary permissions to execute the sequence.

The sequence is either not created or it is removed from the database. When you try to execute the sequence, oracle shows error message as sequence does not exist. It’s possible that the database user lacks the necessary permissions to execute the sequence. If Oracle cannot find the sequence in the database, the error ORA-02289: sequence does not exist will be thrown.

You’re trying to run a sequence that isn’t in the database. There’s also a chance that the sequence will be removed. Before the sequence can be run, it must first be created. If the sequence exists in the database, the database user may not have the necessary permissions to execute it. The sequence name in the database code might be misspelt. The error ORA-02289: sequence does not exist will be thrown if Oracle is unable to locate the sequence with the appropriate permission.

The Problem

If you try to execute a sequence that does not exist in the database with required permission, oracle could not find the sequence in the database schema. Oracle will throw the error. The sequence myseq hasn’t been created yet, therefore try getting the sequence’s current value from the myseq sequence. Because the sequence myseq does not exist in the database, Oracle was unable to locate it. The Sequence error will be thrown.

select myseq.currval from dual;

Error

ORA-02289: sequence does not exist
02289. 00000 -  "sequence does not exist"
*Cause:    The specified sequence does not exist, or the user does
           not have the required privilege to perform this operation.
*Action:   Make sure the sequence name is correct, and that you have
           the right to perform the desired operation on this sequence.
Error at Line: 39 Column: 8

Solution 1

You’re trying to execute a sequence that doesn’t exist. You may have copied the code and attempted to run the sequence with a name that didn’t exist in the database. It’s possible you’ve overlooked the database sequences that haven’t been created yet. You’re attempting to create a sequence with a generic name that doesn’t already exist in the database. This problem will be resolved if you create a sequence in the database before executing.

select myseq.currval from dual;

Solution 2

If the sequence is not created in the database, the error “ORA-02289: sequence does not exist” will be thrown. The sequence should be created in the database before it is invoked. Use the CREATE OR REPLACE SEQUENCE command to create or replace a sequence. The sequence will be created with a starting number and will increment when the next value is called. It’s possible to increase or decrease the sequence’s current value. The sequence might repeat the same number or generate one-of-a-kind values.

create sequence mysequence start with 1 increment by 1 nocycle;

select mysequence.nextval from dual;

Solution 3

When you create a sequence in one schema, you can’t access it in another. The schema name should be used as a prefix when naming the sequence. The sequence will be invoked from the specified schema location. Before invoking the sequence, consider about the schema name where it was created.The schema name is not necessary if you are calling from the same schema where the sequence was created. The current schema name is used by default.

select <scenam_name>.mysequence.nextval from dual;

select hr.mysequence.nextval from dual;

Solution 4

It’s possible that the triggered sequence lacks the necessary privileges and permissions. If the sequence does not have the proper permissions and privileges, Oracle will give an error “ORA-02289: sequence does not exist.” The execute permission should be granted to the database sequence. The grant command is used to provide the necessary permission to the sequence.

create public synonym myseq for hr.mysequence;
select myseq.nextval from dual;

Solution 5

The error “ORA-02289: sequence does not exist” occurs when running a database sequence that does not exist. The query below returns information on the database sequence, including its name and schema. You may find the error in the database sequence and make the required changes. You may need to have the proper permissions to run the query below. If you don’t have adequate permission to run this query, contact your database administrator.

SELECT * FROM all_objects WHERE object_name = 'MYSEQUENCE';