UPDATE and commit;

create or replace PROCEDURE update_testing is
cursor c1 is select a.EMP_ID val_id from LOCATION A ,TEST B WHERE a.REL_DIRECTORY=b.REL_DIRECTORY;
my_counter number(12);
begin
my_counter:=0;
for c2 in c1 loop
UPDATE TEST SET EMP_ID=C2.VAL_ID;
my_counter:=my_counter+1;
if my_counter=50000 then
commit;
my_counter:=0 ;
end if;
COMMIT;
end loop;
end;

This procedure when executed does not not commit after every 50000 records and the procedure fails because of rollback segs ... What is wrong in the procedure ..

i can't see any reason for not doing what you are trying to do i.e commit afer every 50000.

check to see if you are getting back the right result by using dbms_output package after every 10000 records also put some otput statement in if block to check if that block is executed. if that block is executing then there is problem with the records fetched .

Subha,

I do not want to be rude, but your procedure is a MESS. Among other things:

1. You have two commits in it: one that should fire aftere every 50000 updates and another one that fires after *every* update! I suppose you only want to keep the first one and remove the second one.

2. For each record that you read from cursor c1 you perform an update of *all records* in table TEST with the same value VAL_ID. I think you have forgotten to add some WHERE condition in your update clause. As it is now, if you have 500.000 records in test and if your cursor c1 returns 100.000 you are updating 500.000 records 100.000 times. No wonder you are getting some rollback segment error.

You are not saying what error are you getting, but I'm sure it is ORA-1555 "snapshot to old". Now even if you remove your second commit you are pretty good chances you will still get this error, because you are actualy doing "fetch accros commit". There are many threads in this forum explaining the reasons for ORA-1555 and how to avouid them - use the forum's search facility and read those threads.

the above explaination is exactly what I think,
set your arraysize to less and increase your sort_area_size and add a rbs a temp on and then drop it.

Post superceded by...

http://www.dbasupport.com/forums/sho...threadid=12883

- Chris

Waaaw, what a confusion.

Chris, I'm opening this thread as it would make sense to continue on this one rather than the other. If there is a way to merge these two threads it would be really nice.


http://www.goldenplasma.f2s.com/foru.../dilbert16.gif
Sam

[Edited by sambavan on 07-06-2001 at 11:13 AM]

hehe - No way of combining that I know of. We could just copy the other post into here and close *that* thread maybe. - Watcha think?

- Chris

http://www.goldenplasma.f2s.com/foru.../dilbert14.gif : That would be a good idea. Would you go ahead and do it?


:)
Sam

Alrighty then :)

Here is some more (new?) information from Subha:

Quote:

---

SQL> desc location
Name Null? Type
----------------------------------------- -------- -----------------
LOCATION_ID NUMBER(5)
REL_DIRECTORY VARCHAR2(100)

location table has 1200o rows
SQL> desc TEST
Name Null? Type
----------------------------------------- -------- ------------------
BATCH_NAME NUMBER(10)
REL_DIRECTORY VARCHAR2(100)
FILENAME VARCHAR2(50)
REL_DATE DATE
ACC_NUMBER VARCHAR2(26)
DOC_TYPE VARCHAR2(5)
CUST_NAME VARCHAR2(30)
VIN VARCHAR2(20)
C_CODE VARCHAR2(6)
BRANCH_CODE VARCHAR2(8)
IMAGE_TYPE VARCHAR2(2)
ZIP_DATE DATE
SENT_DATE DATE
LOCATION_ID NUMBER(5)

table test has 26 million rows...

update TEST a set a.location_id=(select b.location_id FROM location b WHERE a.rel_directory=b.rel_directory)

this above update works in my development database ...without this procedure ...




create or replace PROCEDURE update_testing is
cursor c1 is select a.EMP_ID val_id from LOCATION A ,TEST B WHERE a.REL_DIRECTORY=b.REL_DIRECTORY;
my_counter number(12);
begin
my_counter:=0;
for c2 in c1 loop
UPDATE TEST SET EMP_ID=C2.VAL_ID;
my_counter:=my_counter+1;
if my_counter=50000 then
my_counter:=0 ;
end if;
COMMIT;
end loop;
end;

This procedure is not working and the results are not correct...
pls correct this procedure

The cursor and the update should commit every 50000 rows .

This i want as urgent as possible

---

Sambavan can you correct the procedure wheer I am wrong ..

Subha,

Please read the reply of Jurij. Unless you change the following statement

"UPDATE TEST SET EMP_ID=C2.VAL_ID; " by having a where clause you are updating 26M records for each execution of this statement.

-amar

Well, here is a cleaned up copy of your code with the corrections that Jurij *alread* told you to make...

CREATE OR REPLACE PROCEDURE
---UPDATE_TESTING
IS
---CURSOR
------C1
---IS
------SELECT
---------L.EMP_ID
------------VAL_ID
------FROM
---------LOCATION L ,
---------TEST ------T
------WHERE
---------L.REL_DIRECTORY---=---T.REL_DIRECTORY;

---MY_COUNTER NUMBER(12);

BEGIN
---MY_COUNTER:=0;
---FOR C2 IN C1 LOOP
------UPDATE
---------TEST
------SET
---------EMP_ID---=---C2.VAL_ID
------WHERE
---------REL_DIRECTORY---=---C2.REL_DIRECTORY;
------MY_COUNTER---:=---MY_COUNTER---+---1---;
------IF ( MY_COUNTER---=---50000 )---THEN
---------MY_COUNTER:=0 ;
---------COMMIT;
------END IF;
---END LOOP;
END;



However, you are *still* fetching across commits. As Jurij *already* said, you should search this forum for maybe 'FETCH ACROSS COMMIT' to find multiple threads where we explain the issue and solutions.

HTH,

- Chris

In your procedure, as far as commit is concerned, you are commiting on every update instead on after 5000. That is because you have placed the commit not in the conditional block


for ....

if .... then
reset the counter;
COMMIT;
end if;

end loop;
--
-- Final commit to confirm
-- that all your data had been commited
--
COMMIT;

end;
/


Hope this would fix your commit issue.

Sam

Chris,

Don't you think that you have to select the REAL_DIRECTORY in your cursor, in-order to alias it in the update?

Sam

Yeah yeah yeah.... minor technicality;)


Good catch with the final COMMIT as well.

- Chris

CREATE OR REPLACE PROCEDURE UPDATE_TESTING
IS
CURSOR C1 IS SELECT L.EMP_ID VAL_ID,L.REL_DIRECTORY l_rel_dir,T.REL_DIRECTORY t_rel_dir FROM LOCATION L , TEST T WHERE
L.REL_DIRECTORY=T.REL_DIRECTORY;
MY_COUNTER NUMBER(12);
BEGIN
MY_COUNTER:=0;
FOR C2 IN C1 LOOP
UPDATE TEST SET EMP_ID=C2.VAL_ID WHERE c2.l_rel_dir=c2.t_rel_dir ;
MY_COUNTER:=MY_COUNTER+1;
IF ( MY_COUNTER=50 )THEN
MY_COUNTER:=0 ;
COMMIT;
END IF;
END LOOP;
commit;
END;

I have created this as said .But this statement goes into a loop ...
The test table had about
SQL> select count(*) from test;

COUNT(*)
----------
44690

SQL> select USED_UREC from v$transaction;

USED_UREC
----------
175417

SQL> /

USED_UREC
----------
182627

and this is not commiting for every 50 records ...

Why would you want to compare the real directory on to that of the cursor?

UPDATE TEST SET EMP_ID=C2.VAL_ID WHERE c2.l_rel_dir=c2.t_rel_dir ;


Shouldn't it be that of

UPDATE TEST SET EMP_ID=C2.VAL_ID WHERE real_directory =c2.t_rel_dir ;

Is there something else we are missing ????

Sam