Home » SQL & PL/SQL » SQL & PL/SQL » Distirbuted Update Operation (Oracle 10g, Unix)
Distirbuted Update Operation [message #431944] Fri, 20 November 2009 03:05 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi experts,

I have a procedure that has run already for 2 days now, because several bulk updates were sent, causing about 5M+ of records must be verified and updated to their tables. A unix script calls the pl/sql procedure. I killed the session in unix and the sessions in oracle. All of the sessions under the osuser are currently in a status = 'KILLED'.

Whenever I recompile the procedure again, its taking alot of time to finish, I thought our tablespace is full, I checked it and its not. I decided to execute the procedure as a manual run. I mean I replaced 'CREATE OE REPLACE PROCEDURE...' with a simple 'DECLARE ...'.

Now, I'm encountering this error:

ORA-02055: distributed update operation failed; rollback required
ORA-06532: Subscript outside of limit


I searched for a solution and a rollback is required. What does it mean by that? I did a simple rollback but it didn't work. I don't have any administator access also.

Please help... Sad


Regards,
Wilbert
Re: Distirbuted Update Operation [message #431945 is a reply to message #431944] Fri, 20 November 2009 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
THe caue of your problem is the ORA-06532 error:
ORA-06532: Subscript outside of limit
    Cause: A subscript was greater than the limit of a varray or non-positive for a varray or nested table.
    Action: Check the program logic and increase the varray limit if necessary.


I'd guess that it is involved in an update over a database link.

Without seeing any of your code, thats about all the help I can be.
Re: Distirbuted Update Operation [message #431947 is a reply to message #431944] Fri, 20 November 2009 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
killed the session in unix and the sessions in oracle. All of the sessions under the osuser are currently in a status = 'KILLED'.

Whenever I recompile the procedure again, its taking alot of time to finish
That suggests that one of your killed sessions is still rolling back, or was involved in a distributed transaction and is waiting for the remote transaction to die/be killed. Try tracking down the remote sessions and killing them.

Quote:
I thought our tablespace is full, I checked it and its not
Packages / Procedures do not live in tablespaces. This is not going to be the cause of the problem.
Re: Distirbuted Update Operation [message #435388 is a reply to message #431945] Wed, 16 December 2009 00:54 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi JRowbottom,

Sorry it took me a while to reply. Anyway this was resolved and your intial comment on ORA-06532 error was correct, i forgot that i've set the limit of bulk input files for processing to 800 while the input files during that time were 1K+.

Thanks and Regards,
Wilbert
Previous Topic: Extracting data from BLOB data type (merged)
Next Topic: Parallel Select
Goto Forum:
  


Current Time: Mon Dec 05 18:57:10 CST 2016

Total time taken to generate the page: 0.13404 seconds