Home » SQL & PL/SQL » SQL & PL/SQL » Update Hangs shared memory cannot be read!!! (xp,oracle 9i)
Update Hangs shared memory cannot be read!!! [message #313492] Sun, 13 April 2008 03:06 Go to next message
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 #313503 is a reply to message #313492] Sun, 13 April 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Copy and paste your session.
Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Update Hangs shared memory cannot be read!!! [message #313528 is a reply to message #313503] Sun, 13 April 2008 08:29 Go to previous messageGo to next message
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 #313537 is a reply to message #313528] Sun, 13 April 2008 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ It is not your session EXECUTION. I want to see the result of each statement.
2/ It is not formatted as explained in guide. Use code tags.
3/ You didn't use preview button otherwise you'd see it.

Regards
Michel
Re: Update Hangs shared memory cannot be read!!! [message #313551 is a reply to message #313537] Sun, 13 April 2008 12:50 Go to previous messageGo to next message
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 #313552 is a reply to message #313551] Sun, 13 April 2008 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the session and format it.

Regards
Michel
Re: Update Hangs shared memory cannot be read!!! [message #313593 is a reply to message #313492] Sun, 13 April 2008 20:37 Go to previous messageGo to next message
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 #313595 is a reply to message #313492] Sun, 13 April 2008 20:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
if you won't follow posting guidelines, then we won't answer your question(s).
Re: Update Hangs shared memory cannot be read!!! [message #313662 is a reply to message #313492] Mon, 14 April 2008 01:44 Go to previous message
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?
Previous Topic: regarding shift operator
Next Topic: sqlplus can't start
Goto Forum:
  


Current Time: Sat Nov 09 16:35:23 CST 2024