Home » SQL & PL/SQL » SQL & PL/SQL » "ORA 3114 Not connected to oracle" error after an "ORA-00054: resource busy and acqui
"ORA 3114 Not connected to oracle" error after an "ORA-00054: resource busy and acqui [message #187649] Mon, 14 August 2006 16:03 Go to next message
floco
Messages: 2
Registered: August 2006
Junior Member
I have a PL/SQL function that returns a sequence number reading from a table. I can have many sessions using the function at the same time. In this function just before the SELECT i have a LOCK TABLE ... IN EXCLUSIVE MODE NOWAIT, because i don't want a session waiting a lot of seconds for the table. If the ORA-00054 error is generated i retry to read the sequence 100 times. If the erros stills after this 100 times i don't retry again. So, the error ORA-00054 can be generated a lot of times. My question is: May this situation do a crash of the session and generate a ORA-3114 error ?

P.D. Excuse my bad english, but my native language is spanish...
Re: "ORA 3114 Not connected to oracle" error after an "ORA-00054: resource busy and a [message #187656 is a reply to message #187649] Mon, 14 August 2006 16:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you well, this function returns "MAX(some_column) + 1" from some table. But, if SELECT is the only thing it does, why would you need to LOCK the table? Any number of users can perform SELECT operation on the same data set, and there will be no conflict between them.

But, if you are fetching "MAX + 1" and inserting it into the table, this might (and, probably will) cause table to be locked.

Workaround? The idea itself is wrong. Don't use MAX + 1 to generate a sequence number, but use sequences instead. That's why there are designed for. Then you wouldn't need function but use

SELECT seq.nextval INTO some_variable FROM dual;

or similar.
Re: "ORA 3114 Not connected to oracle" error after an "ORA-00054: resource busy and a [message #187663 is a reply to message #187649] Mon, 14 August 2006 17:36 Go to previous messageGo to next message
floco
Messages: 2
Registered: August 2006
Junior Member
Littlefoot, thanks for your answer. My problem is that the sequence i need depends of the value of another field of the table. F. example: SELECT SEQ +1 FROM XXX WHERE OFFICE = 123; Then, i can´t use a oracle sequence, because the number i need is different for each office.
Re: "ORA 3114 Not connected to oracle" error after an "ORA-00054: resource busy and a [message #187718 is a reply to message #187663] Tue, 15 August 2006 02:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you not have one sequence per office, or construct a number of the type OFFICE_ID * 100,000 + SEQ_NUMBER.

Your current solution scales very poorly (as you have discovered). If you give us more detail we may be able to work something out.
Previous Topic: pls
Next Topic: Bulk Longest Match Query
Goto Forum:
  


Current Time: Sun Dec 11 02:39:45 CST 2016

Total time taken to generate the page: 0.18516 seconds