OraFAQ Forum: SQL & PL/SQL » ORA-30036 while running a Procedure

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30036 while running a Procedure (9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-30036 while running a Procedure [message #638263]

Mon, 08 June 2015 10:18

Jaise
Messages: 6
Registered: August 2012
Location: India

Junior Member

Hi,

I am trying to update all column with zero but it is failing with error ORA-30036. The procedure I am using is

begin
 FOR i in 1985..2015 loop
EXECUTE IMMEDIATE 'update TEMP set RESULT_'||i|| '= 0';
END LOOP;
COMMIT;
END;

I have the columns name RESULT_1985, RESULT_1986,...,RESULT_2015 and have a half million row in the table. Intend to add zero is to ease the calculation which I will be doing later. Does this work properly if I commit after every 1000 update

Report message to a moderator

Re: ORA-30036 while running a Procedure [message #638264 is a reply to message #638263]

Mon, 08 June 2015 10:32

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-30036: unable to extend segment by %s in undo tablespace '%s'
 *Cause:   the specified undo tablespace has no more space available.
 *Action:  Add more space to the undo tablespace before retrying
           the operation. An alternative is to wait until active
           transactions to commit.

Report message to a moderator

Re: ORA-30036 while running a Procedure [message #638265 is a reply to message #638263]

Mon, 08 June 2015 10:33

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I am trying to update all column with zero but it is failing with error ORA-30036. The procedure I am using is

Do it in a single UPDATE.
You know you can update several columns in a single UPDATE statement.

Report message to a moderator

Re: ORA-30036 while running a Procedure [message #638270 is a reply to message #638263]

Mon, 08 June 2015 13:49

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Jaise wrote on Mon, 08 June 2015 10:18

Hi,

I have the columns name RESULT_1985, RESULT_1986,...,RESULT_2015 and have a half million row in the table. Intend to add zero is to ease the calculation which I will be doing later. Does this work properly if I commit after every 1000 update

So by definition your table is not properly designed to 3d Normal Form. All of those repeating 'result' columns need to be broken out as individual rows in a child table. Your current design is awkward and unscalable. Do you add a new column every year?

A proper Third Normal Form design will eliminate your problem -- and many others as well.

Report message to a moderator

Re: ORA-30036 while running a Procedure [message #638274 is a reply to message #638265]

Mon, 08 June 2015 14:19

Jaise
Messages: 6
Registered: August 2012
Location: India

Junior Member

I am now trying using an update statement and it is running now. The temp table have 150 columns (Year) and half a million row (assets) to update. But I have to find a work around for the procedure because I will using a similar one sooner. It will not be for updating zero but for counting the number of activities against each asset in an year.
Whether this will help?

DECLARE
j NUMBER = 0
begin
FOR i in 1985..2015 loop
EXECUTE IMMEDIATE 'update TEMP set RESULT_'||i|| '= 0';
j=j+1;
IF j>2 THEN
COMMIT;
J=0
END IF
END LOOP;
COMMIT;
END;

Report message to a moderator

Re: ORA-30036 while running a Procedure [message #638275 is a reply to message #638274]

Mon, 08 June 2015 14:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do what I said: one and only one UPDATE.
Or do what Ed said: fix your model.

Note that committing every couple of DML is really STUPID.

Report message to a moderator

Re: ORA-30036 while running a Procedure [message #638276 is a reply to message #638275]

Mon, 08 June 2015 14:31

Jaise
Messages: 6
Registered: August 2012
Location: India

Junior Member

Michel Cadot wrote on Mon, 08 June 2015 23:24

Do what I said: one and only one UPDATE.
Or do what Ed said: fix your model.

Note that committing every couple of DML is really STUPID.

This is not my database design. I am actually trying to take an extract of the 'count of number of activities' against my half million assets per year and this will be a one time activity. My aim update the count against each asset in the table and to take a CSV extract of the table.

Report message to a moderator

Re: ORA-30036 while running a Procedure [message #638277 is a reply to message #638276]

Mon, 08 June 2015 14:55

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You learn good programming on one-shot procedure.
You learn bad programming in the same way.

Report message to a moderator

Previous Topic:	ORA-02437: cannot validate (TAGICRM.PK_RISK_GRID) - primary key violated
Next Topic:	Fast query paging for large table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:36:11 CDT 2024