Share |

ORA-02289: Sequence does not exist

The specified sequence does not exist, or the user does not have the required privilege to perform this operation.

Make sure the sequence name is correct, and that you have the right to perform the desired operation on this sequence.

SQL> select unknown_sequence.nextval from dual;
select unknown_sequence.nextval from dual
       *
ERROR at line 1:
ORA-02289: sequence does not exist

First you will need to check if the sequence exists:

select * from all_sequences where sequence_name='UNKNOWN_SEQUENCE';

The all_sequences list all sequences for which you have select privileges.

If the sequence does not show up, you can query the dba_sequences view, which lists all sequences in the system:

select * from dba_sequences where sequence_name='UNKNOWN_SEQUENCE';

If the sequence does show up, there are 2 possibilities:

You have the necessary privileges to select the sequence. If this is the case, and the sequence is owned by a different user then the user you are doing the select with, prepend the sequence with the owner.
You do not have the necessary privileges to select from the sequence, and you are not the owner of the sequence. You will need to grant the select privilege to the user who wants to select the nextval or currval

grant select on ownerofsequence.sequence_name to selector_of_sequence;

If the sequence does not show up when you query the dba_sequences table, you will need to create it (and possibly grant select privileges to other users):

create sequence my_new_sequence;
grant select on my_new_sequence to another_user;

Welcome to our forum for Oracle error: ORA-02289 Add your own message

On 19-MAR-2008 03:58:23 Yogita asked: Regarding ORA-02289 'Sequence does not exit

Reply

"The specified sequence does not exist, or the user does not have the required privilege to perform this operation. "

What this exactly means?

On 19-MAR-2008 05:37:23 DbMotive replied

Reply

SQL> grant create session, create sequence to user1 identified by user1;

Grant succeeded.

SQL> grant create session to user2 identified by user2;

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> create sequence s_test;

Sequence created.

SQL> select s_test.nextval from dual;

NEXTVAL
----------
1

==> user2 will not be able to access the synonym because he has no privileges

SQL> conn user2/user2
Connected.
SQL> select user1.s_test.nextval from dual;
select user1.s_test.nextval from dual
*
ERROR at line 1:
ORA-00942: table or view does not exist

==> This will not work because the synonym is not owned by user2, but by user1

SQL> select s_test.nextval from dual;
select s_test.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist

==> now we grant access on the synonym to user2

SQL> conn user1/user1
Connected.
SQL> grant select on s_test to user2;

Grant succeeded.

==> now user2 has the necessary privileges to access the synonym owned by user1

SQL> conn user2/user2@entor28
Connected.
SQL> select user1.s_test.nextval from dual;

NEXTVAL
----------
21

On 19-OCT-2008 09:34:42 izham asked: ORA-02289: sequence does not exist

Reply

cannot insert data into table

"INSERT INTO staff (staff_id)
VALUES (staff_seq.nextval)"

Add your message

Ask Your Question

If you need more information about this particular error message, you can leave a forum message.

We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.

Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.

Spam Protection

In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.

If you do not enter the validation correctly, your message will not be recorded.

Forum Rules

Please be polite, do not USE ALL UPPERCASE, no insults, violance or any other threats.