ORA-02289: sequence does not exist

ORA-02289: sequence does not exist error occurs when the requested sequence does not exist, or the user does not have the necessary privileges to execute this sequence. Check the sequence name is valid, and that you have the necessary permissions to execute the required operation on this sequence.

The sequence is either not created or removed from the database. When you try to execute the sequence, it says sequence does not exist. The database user may not have the necessary permissions to run 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 execute a sequence that doesn’t exist in the database. It’s also possible that the sequence will be deleted. The sequence must first be created before it can be executed. The database user may not have the appropriate rights to execute the sequence if it exists in the database. In the database code, the sequence name might be misspelt. If Oracle is unable to find the sequence with the appropriate permission, the error ORA-02289: sequence does not exist will be thrown.



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.

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 run a sequence that doesn’t exist. It’s possible that you copied the code and tried to execute the sequence with a name that didn’t exist in the database. It’s possible that you’ve ignored the database sequences that have yet to be produced. You try to create a sequence with a generic name that doesn’t exist in the database. If you create a sequence in the database before running, this error will be addressed.

select myseq.currval from dual;


Solution 2

The ORA-02289: sequence does not exist error will be thrown if the sequence is not created in the database. Before invoking the sequence, it should be created in the database. To create a sequence, use the CREATE OR REPLACE SEQUENCE command.

create sequence mysequence start with 1 increment by 1 nocycle;

select mysequence.nextval from dual;


Solution 3

When a sequence is created in one schema, it cannot be accessed in another schema. The sequence should be named using the schema name as a prefix. The sequence will be invoked from the schema location specified. Consider the schema name where the sequence was created before invoking it.

select <scenam_name>.mysequence.nextval from dual;

select hr.mysequence.nextval from dual;


Solution 4

It’s possible that the sequence that was triggered lacked the appropriate privileges and permissions. Oracle will throw the error “ORA-02289: sequence does not exist” if the sequence does not have the appropriate permissions and privileges. In the database, the execute permission should be added to the sequence.

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


Solution 5

If an error “ORA-02289: sequence does not exist” occurs, when running a database sequence that does not exist. The following query will provide details about the database sequence, including its name and schema. You may find the error in the database sequence and make the required modifications.

SELECT * FROM all_objects WHERE object_name = 'MYSEQUENCE';