Hi all,

I created the context INDEX on the snapshot tables as follows;

BEGIN
ctx_ddl.create_preference('MYSTORAGE','BASIC_STORA GE');
--I_TABLE_CLAUSE---
tbs_name := 'tablespace &&CTX_TABLESPACE_I_NAME INITRANS 3 MAXTRANS 255 storage (initial

512K NEXT 512K pctincrease 0)';
ctx_ddl.set_attribute('MYSTORAGE', 'I_TABLE_CLAUSE',tbs_name);

--K_TABLE_CLAUSE---
tbs_name := 'tablespace &&CTX_TABLESPACE_K_NAME INITRANS 3 MAXTRANS 255 storage (initial

512K next 512K pctincrease 0)';
ctx_ddl.set_attribute('MYSTORAGE', 'K_TABLE_CLAUSE',tbs_name);

--R_TABLE_CLAUSE--- For Preloading into cache.
tbs_name := 'tablespace &&CTX_TABLESPACE_R_NAME INITRANS 3 MAXTRANS 255 storage (initial 5M

NEXT 5M pctincrease 0) lob (data) store as (cache)';
ctx_ddl.set_attribute('MYSTORAGE', 'R_TABLE_CLAUSE',tbs_name);

--N_TABLE_CLAUSE---
tbs_name := 'tablespace &&CTX_TABLESPACE_N_NAME INITRANS 3 MAXTRANS 255 storage (initial

512K NEXT 512K pctincrease 0)';
ctx_ddl.set_attribute('MYSTORAGE', 'N_TABLE_CLAUSE',tbs_name);

--i_index_clause---
tbs_name := 'tablespace &&IX_TABLESPACE_NAME INITRANS 3 MAXTRANS 255 storage (initial 512K

NEXT 512K pctincrease 0)';
ctx_ddl.set_attribute('MYSTORAGE', 'i_index_clause', tbs_name);

--P_TABLE_CLAUSE---
tbs_name := 'tablespace &&IX_TABLESPACE_NAME INITRANS 3 MAXTRANS 255 storage (initial 512K

NEXT 512K pctincrease 0)';
ctx_ddl.set_attribute('MYSTORAGE', 'P_TABLE_CLAUSE',tbs_name);

END;

My TABLE have the following COLUMNS


Description VARCHAR2(256)
Long_Description VARCHAR2(2000);

What I am doing is Delete the existing index first then 'COMPLE' Refresh the data into the
above table through materialized view and build the index again by the following command.

CREATE INDEX index_name ON TABLE_NAME(Long_Description)
INDEXTYPE IS CTXSYS.CONTEXT
parameters('storage MYSTORAGE');

Materialized view Refresh Method is:

dbms_mview.REFRESH ('Table_Name',
'C',
'',
TRUE,
FALSE,
0,
0,
0,
TRUE
);

Note:- I am not quering this Table while Refreshing.

In the Database DB_CACHE_SIZE = 24M
and DB_KEEP_CACHE_SIZE = 0

Some times not always, While creating the INDEX I found the following error:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drekmap (lob write)
ORA-00060: deadlock detected while waiting for resource.


Some times also I am getting the problem like

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drekmap (lob write)
ORA-01403: no data found

The Trace File contents are as follows:-

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
DEADLOCK DETECTED
No current SQL statement being executed.
----- PL/SQL Call Stack -----
object line object
handle number name
6A63574C 244 package body CTXSYS.DRIDISP
6A63574C 412 package body CTXSYS.DRIDISP
6A63574C 369 package body CTXSYS.DRIDISP
6A6B89B8 744 package body CTXSYS.DRIDDL
6A4D7578 152 CTXSYS.TEXTINDEXMETHODS
68DBE630 1 anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL.
The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0008002e-00015f70 23 35 X 23 35 S
session 35: DID 0001-0017-00001B28 session 35: DID 0001-0017-00001B28
Rows waited on:
Session 35: no row
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
================================================== =

Can any body suggest what is possible reason.


Thanking you for ur early reply,
Sankaram