Re: Context-Error ORACLE 8.1.5

From: timkarnold <timkarnold_at_home.com>
Date: Tue, 11 Dec 2001 13:48:39 GMT
Message-ID: <X8oR7.107607$Ze5.52733388@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

"Andre Klos" <Andre.Klos_at_pikon.com> wrote in message news:9v4mp4$g3m$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:

>


> ORA-20000: ConText error:

> DRG-50610: internal error: drexdsync

> DRG-50857: oracle error in drekwn
> ORA-00060: deadlock detected while waiting for resource

> Can someone solve this problem?

> Thanks

> André Klos

>
>
>
>

Received on Tue Dec 11 2001 - 07:48:39 CST