Update Hangs shared memory cannot be read!!! [message #313492] |
Sun, 13 April 2008 03:06 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Hi
I had written this simple procedure but i found that it is not updating,it hangs the moment it enters the loop,Let me know if the update statement is correct??
In test_perform and test2_perform tables i have 1623456 records
I get the following error also after almost an half an hr.
NOT CONNECTED TO ORACLE
CANNOT READ SHARED MEMORY
create or replace procedure insert_bulk
as
ln_counter number:=0;
res long;
CURSOR INSERT_CUR IS
SELECT NAME,VALUE FROM TEST_PERFORM;
TYPE NAME_TAB IS TABLE OF NUMBER(10,2);
V_NAME NAME_TAB;
TYPE VALUE_TAB IS TABLE OF VARCHAR2(30);
V_VALUE VALUE_TAB;
begin
OPEN INSERT_CUR;
LOOP
FETCH INSERT_CUR BULK COLLECT INTO V_NAME,V_VALUE
LIMIT 10000;
res:=V_name.COUNT;
dbms_output.put_line(res);
forALL I in 1..V_VALUE.COUNT
/*insert into test2_perform
values
(V_NAME(I),V_VALUE(I));*/
[b]update test2_perform set v_name=v_value(I);[/b]
commit;
V_NAME.DELETE;
V_VALUE.DELETE;
EXIT WHEN INSERT_CUR%NOTFOUND;
END LOOP;
exception
when others then
dbms_output.put_line(sqlerrm||sqlcode);
--ln_counter:=ln_counter+1;
end;
What is the problem?
|
|
|
|
Re: Update Hangs shared memory cannot be read!!! [message #313528 is a reply to message #313503] |
Sun, 13 April 2008 08:29 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
CREATE OR REPLACE PROCEDURE Insert_Bulk
AS
ln_Counter NUMBER := 0;
res LONG;
CURSOR Insert_Cur IS
SELECT NAME,
VALUE
FROM Test_Perform;
TYPE Name_Tab IS TABLE OF NUMBER(10,2);
v_Name NAME_TAB;
TYPE Value_Tab IS TABLE OF VARCHAR2(30);
v_Value VALUE_TAB;
BEGIN
OPEN Insert_Cur;
LOOP
LOOP
FETCH Insert_Cur BULK COLLECT INTO v_Name,v_Value
Limit 10000;
res := v_Name.COUNT;
dbms_Output.Put_Line(res);
FORALL i IN 1..v_Value.COUNT
UPDATE Test2_Perform SET v_Name=v_Value(i); -- LOOK HERE!!!
COMMIT;
v_Name.DELETE;
v_Value.DELETE;
EXIT WHEN Insert_Cur%NOTFOUND;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_Output.Put_Line(SQLERRM
||SQLCODE);
--ln_counter:=ln_counter+1;
END;
That session is closed,I don't know how to track the sessions,do let me know.
I have formatted it.
|
|
|
|
Re: Update Hangs shared memory cannot be read!!! [message #313551 is a reply to message #313537] |
Sun, 13 April 2008 12:50 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
HI Michel
As the cursor table test_perform contained
1623456 rows.
It was like this
Value1 Value2
1 111
2 111
3 111
. .
. .
. .
It was updating test2_perform with similar values
Value1 V_name
1 444
2 444
3 444
. .
. .
. .
Both the tables contained 1623456 records,So the update statement
UPDATE Test2_Perform SET V_Name=v_Value(i);
I hope you can get it now,Now it hanged so badly that now i get these errors and my database instance has stopped,when i try to strat from the enterprise manager it doesn't
ORA-01034: ORACLE not available
ORA-03113: end-of-file on communication channel
|
|
|
|
Re: Update Hangs shared memory cannot be read!!! [message #313593 is a reply to message #313492] |
Sun, 13 April 2008 20:37 |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
I am unable to connect to oracle because of the crash!!
But i have posted the values from both the tables
It goes something like this for both the tables
select * from test_perform;
Value1 Value2
1 111
2 111
3 111
. .
. .
. .
ORA-01034: ORACLE not available
ORA-03113: end-of-file on communication channel
|
|
|
|
Re: Update Hangs shared memory cannot be read!!! [message #313662 is a reply to message #313492] |
Mon, 14 April 2008 01:44 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> What is the problem?
> UPDATE Test2_Perform SET v_Name=v_Value(i); -- LOOK HERE!!!
Yes you have a problem. You did not issue WHERE clause, so for each row in collection you update all rows in the table. So either issue WHERE clause to choose the proper row to UPDATE or you may just UPDATE whole table with the last collection value (V_VALUE(V_VALUE.COUNT)) - it will be much more performant.
Just a (not very generic) demonstration of this issue: SQL> create table t2( c1 integer, c2 varchar2(30) );
Table created.
SQL> insert into t2( c1, c2 )
2 select level, 'row '||to_number(level) from dual
3 connect by level < 5;
4 rows created.
SQL> select * from t2;
C1 C2
---------- ------------------------------
1 row 1
2 row 2
3 row 3
4 row 4
SQL> declare
2 TYPE Value_Tab IS TABLE OF VARCHAR2(30);
3 v_Value VALUE_TAB := VALUE_TAB( 'X1', 'X2', 'X3', 'X4' );
4 begin
5 FORALL i IN 1..v_Value.COUNT
6 UPDATE t2 SET c2 = v_Value(i);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select * from t2;
C1 C2
---------- ------------------------------
1 X4
2 X4
3 X4
4 X4
SQL> declare
2 TYPE Value_Tab IS TABLE OF VARCHAR2(30);
3 v_Value VALUE_TAB := VALUE_TAB( 'X1', 'X2', 'X3', 'X4' );
4 begin
5 FORALL i IN 1..v_Value.COUNT
6 UPDATE t2 SET c2 = v_Value(i)
7 WHERE c1 = to_number( substr( v_value(i), 2 ) );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select * from t2;
C1 C2
---------- ------------------------------
1 X1
2 X2
3 X3
4 X4
SQL> drop table t2;
Table dropped.
SQL> By the way, you were asked to show similar output of YOUR session. Why are you refusing to post it here?
|
|
|