ORA-02289

[AndrewReid]

I created an Oracle database. I ran the setup script from the web site but I forgot to grant create sequence to the user. The developers then started getting ORA-02289 messages. I traced their sessions and narrowed down the problem to this:
PARSE ERROR #25:len=49 dep=0 uid=87 oct=3 lid=87 tim=39199596750914 err=2289
SELECT Object_ID_seq.nextval AS next_id FROM dual
I granted the create sequence privilege but they are still getting the same message. Does anybody know what I might have done wrong?

[qwerty]

I'm no Oracle specialist but EA users need to be granted almost all rights except to create/delete tables.

q.

[AndrewReid]

Thanks for your reply. The problem was caused because the EA user did not have the CREATE SEQUENCE privilege at the point when the first project transfer was carried out. I granted this privilege then did another test project transfer and the user now has 19 sequences.
   

[Bill McCracken]

I'm no Oracle specialist but EA users need to be granted almost all rights except to create/delete tables.

q.

This is not correct - our installation have basic rights for users and they are operating with normal workflow, albeit slow on oracle 11g.

[Bill McCracken]

Thanks for your reply. The problem was caused because the EA user did not have the CREATE SEQUENCE privilege at the point when the first project transfer was carried out. I granted this privilege then did another test project transfer and the user now has 19 sequences.
   

Just a tip - we also noticed on our Oracle install that the Cursors needed some tuning.  Be sure and check this.
http://www.orafaq.com/node/758

[qwerty]

This is not correct - our installation have basic rights for users and they are operating with normal workflow, albeit slow on oracle 11g.

Sure. But what is "basic rights". Most problems arise from missing privileges. So grant everything and reduce stepwise. Maybe you could publish what you have granted on the Community site. Other DBAs would love you for that.

q.

[AndrewReid]

Thanks for all your help. The ORA-02289 has now gone but performance is very poor. I looked at a post at www.orafaq.com then altered open_cursors to 1000 but it made no difference. It seems to be spending a large percentage of its time in the CPU:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      338     40.70      46.34          0          0          0           0
Execute    223      5.96       6.86          0          0          0           0
Fetch      385     68.65      85.13          5    7737296          0        1142
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      946    115.31     138.34          5    7737296          0        1142[size=8][/size][size=9][/size][size=8][/size]