Solved

ORA-02289: sequence does not exist

Posted on 2011-02-24
5
4,683 Views
Last Modified: 2012-05-11
I've inherited another developer's code and this query:

select id_seq.nextval from dual

Is causing this 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: 1 Column: 7

I'm pretty sure the user has the required privileges to run this query. How can I fix this?

Thanks.
0
Comment
Question by:greddin
  • 3
  • 2
5 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34971216
3 possibilities

the sequence really does not exist   - check dba_sequences
the user you are using doesn't have privileges   -check dba_tab_privs
a synonym pointing to the sequence doesn't exist  - check dba_synonyms
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34971249
it must be at least 1 of those 3.

When looking for existence, check for misspellings including possible case sensitivity.

For example did somebody create the sequence as "id_seq"  instead of id_seq?  
0
 

Author Comment

by:greddin
ID: 34971687
Thanks, do I need to use a system user to access and view these dba_sequences, dba_tab_privs, and dba_synonyms tables?

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34971801
you will need to have privileges to read those,  doesn't necessarily mean SYSTEM or other DBA users though.


you can try using the "ALL_" versions instead of "DBA_" versions of the views, but, those are based on permissions.  So, if you don't have permission to read the object then you won't see them in the ALL_ views either, thus giving a false-negative.

If you don't have permission yourself, ask have to ask your DBA or another privileged user to do it for you.
0
 

Author Closing Comment

by:greddin
ID: 34972926
The sequence didn't exist on this database yet. DBA migrated them over now and all is good.

Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now