Re: Context-Erorr ORACLE 8.1.5
Date: Tue, 11 Dec 2001 13:47:19 GMT
Message-ID: <H7oR7.107603$Ze5.52732558_at_news1.rdc1.md.home.com>
From MetaLink
Oracle Server Enterprise Edition Technical Forum
Displayed below are the messages of the selected thread.
Thread Status: Closed
From: bernhard.schaefers_at_finkundpartner.de 04-May-00 22:10 Subject: update on an table with context indexes doesn't work, ORA-00604
RDBMS Version: 8.1.5
Operating System and Version: NT 4.0 SP 5
Error Number (if applicable): ORA-00604
Product (i.e. SQL*Loader, Import, etc.): interMedia Text
Product Version: 8.1.5.0.3
update on an table with context indexes doesn't work, ORA-00604
Hi folks,
I use a table with 8 context indexes and 40.000 tupels. The indexes work fine: I tested select, insert and update with SQL*Plus: Everything is ok.
In a PL/SQL procedure I'm doing a lot of updates (100-300) on a column which doesn't have a context index. Every update is immediatly committed. In this case I get sometimes an exception. I tried to reproduce this with SQL*Plus:
SQL> begin
2 for i in 405300 .. 405400 loop
3 update bilder set statusarchid = 0
4 where bilderid = i;
5 commit;
6 end loop;
7 end;
8 /
begin
*
ERROR in line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-20000: ConText error: DRG-50610: internal error: drexdsync DRG-50857: oracle error in drekwn ORA-00060: deadlock detected while waiting for resource ORA-06512: in "CTXSYS.SYNCRN", line 0 ORA-06512: in line 1 ORA-06512: in line 5
Does someone know the reason for this behaviour? Is there a solution or a workaround? Thank you very much for your help, Bernhard
From: bernhard.schaefers_at_finkundpartner.de 05-May-00 23:02 Subject: problems identified, probably context-bugs...
Hi there,
I identified the problems I had, they are probably context-bugs: 1) ERROR occurs if there are lot of immediately committed updates 2) context indexed column is used in row level update trigger: for every update statement the the context index will be updated, necessary or not
Does someone know a good workaround? The following code reproduces the problem. Thanks, Bernhard...
- create table alter session set nls_language=american / drop table test / create table test ( test_id number constraint pk_test primary key, text varchar2 (2000), another_id number ) /
- insert some data begin for i in 1..1000 loop insert into test values (i, 'this is tupel no ' || to_char (i), 0); end loop; commit; end; /
- create simple word index on test.text begin Ctx_Ddl.Drop_Preference ('my_lexer'); end; / begin Ctx_Ddl.Create_Preference ('my_lexer', 'basic_lexer' ); Ctx_Ddl.Set_Attribute ('my_lexer', 'index_text', 'true' ); Ctx_Ddl.Set_Attribute ('my_lexer', 'index_themes', 'false'); end; / select err_text, err_index_name from ctx_user_index_errors; create index dtx_test_text on test (text) indextype is ctxsys.context parameters ('lexer my_lexer'); select err_text, err_index_name from ctx_user_index_errors;
- everything ok until now..
- now we will produce an error...
- just a lot of updates which are committed immediately begin for i in 1..1000 loop update test set text = 'shit happens' where test_id = i; commit; end loop; end; /
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-20000: ConText error: DRG-50610: internal error: drexdsync DRG-50857: oracle error in drekwn ORA-00060: deadlock detected while waiting for resource ORA-06512: at "CTXSYS.SYNCRN", line 0 ORA-06512: at line 1 ORA-06512: at line 5 -- now I will show you, that you can get this error with -- an update on a column without context index...-- first I will show you that an update on text.another_id -- doesn't have an effect to ctx_user_pending update test set another_id = '666' where test_id = 100 /
select count (*), pnd_index_name from ctx_user_pending group by pnd_index_name
/
commit
/
- create a trigger with an assignment to the context indexed column text
- this assignment is dead code, but this doesn't matter, Context thinks
- that every time when there is an update on test it must fill ctx_user_pending create or replace trigger tr_test_bur before update on test for each row begin if false then :new.text := 'this will be never executed'; end if; end; /
- update on another_id now fills ctx_user_pending update test set another_id = '666' where test_id = 100 / select count (*), pnd_index_name from ctx_user_pending group by pnd_index_name / commit /
- now I can produce an error just updating another_id... begin for i in 1..1000 loop update test set another_id = '666' where test_id = i; commit; end loop; end; /
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-20000: ConText error: DRG-50610: internal error: drexdsync DRG-50857: oracle error in drekwn ORA-00060: deadlock detected while waiting for resource ORA-06512: at "CTXSYS.SYNCRN", line 0 ORA-06512: at line 1 ORA-06512: at line 5 ----------------------------------------------------------------------------
From: Oracle, Helen Schoone 09-May-00 22:26 Subject: Re : update on an table with context indexes doesn't work, ORA-00604 Hi. This appears to be due to an interMedia bug (906273). This bug is fixed in 8.1.6. A request for a patchset exception (e.g. individual patch) for 8.1.5.0 NT has been submitted (1102910), but is not yet available.
8.1.6 is currently shipping for NT.
I hope this helps.
Regards,
Helen
Oracle Server EE Analyst
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
"Andre Klos" <Andre.Klos_at_pikon.com> wrote in message
news:9v4mqo$g5m$1_at_piesbach.saarnet.de...
> Hi!
>
> I try to update 80.000 rows of a table with a ctxsys.context index. We
also
> have started a context server to synchronize this index with alter index
at
> regular intervals. When I start the update, I receive the following error
> messages:
>> DRG-50857: oracle error in drekwn
> ORA-20000: ConText error:
> DRG-50610: internal error: drexdsync
> ORA-00060: deadlock detected while waiting for resource
>
> Can someone solve this problem?
>
> Thanks
>
> André Klos
> > > >Received on Tue Dec 11 2001 - 14:47:19 CET