Home » Server Options » Text & interMedia » getting error ora-29855 AND Error while creating Index on clob data (2 threads merged by bb)
getting error ora-29855 AND Error while creating Index on clob data (2 threads merged by bb) [message #376574] Wed, 17 December 2008 22:40 Go to next message
asksrikanth@gmail.com
Messages: 9
Registered: December 2008
Location: Bangalore
Junior Member
Hi,


When i am executing the below command

CREATE INDEX ICD9_DATA_INDEX ON EMRENCOUNTERDATA
(ICD9_DATA) INDEXTYPE IS CTXSYS.CONTEXT

i am getting the error as below

Error starting at line 7,238 in command:
CREATE INDEX ICD9_DATA_INDEX ON EMRENCOUNTERDATA
(ICD9_DATA)
INDEXTYPE IS CTXSYS.CONTEXT
Error report:
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Please do needful.Thanks in advance


Sri

Report message to a moderator

getting error ora-29855 [message #376575 is a reply to message #376574] Wed, 17 December 2008 22:42 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Error starting at line 7,238 in command:
CREATE INDEX ICD9_DATA_INDEX ON EMRENCOUNTERDATA
(ICD9_DATA)
INDEXTYPE IS CTXSYS.CONTEXT
Error report:
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Report message to a moderator

Re: Error while creating Index on clob data [message #376577 is a reply to message #376574] Wed, 17 December 2008 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00955: name is already used by an existing object

Regards
Michel

Report message to a moderator

Re: Error while creating Index on clob data [message #376580 is a reply to message #376574] Wed, 17 December 2008 23:21 Go to previous messageGo to next message
asksrikanth@gmail.com
Messages: 9
Registered: December 2008
Location: Bangalore
Junior Member
Hi Michel,

Before running the Create Index query i execute the below query to find any indexes exist

Select * from all_objects where object_name = 'ICD9_DATA_INDEX'

and the query is returning nothing.

Report message to a moderator

Re: getting error ora-29855 [message #376585 is a reply to message #376575] Wed, 17 December 2008 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00955: name is already used by an existing object


Regards
Michel

Report message to a moderator

Re: getting error ora-29855 [message #376590 is a reply to message #376585] Wed, 17 December 2008 23:44 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
HAI MICHEL,

IAM STILL GETTING THE ERROR EVEN I DROPPED THAT PARTICULAR INDEX,MAY PROBLEM PERSIST WITH THE INDEX TYPE i.e CONTEXT.SYS SPECIFIED.

THANKS,
RAJASEKHAR

Report message to a moderator

Re: Error while creating Index on clob data [message #376594 is a reply to message #376580] Thu, 18 December 2008 00:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9082
Registered: November 2002
Location: California, USA
Senior Member
Sometimes there are problems and not all of the things associated with an index, like some of the dr$...$... tables get dropped. I have seen cases where the index is supposedly dropped, but some of the domain tables still exist. The fact that you are getting an error on "create_index_tables" suggests that this is the problem. See what the following query returns.

Select * from all_objects where object_name LIKE '%ICD9_DATA_INDEX%';

If you find any objects like DR$ICD9_DATA_INDEX$... then try to drop them individually. You may have to use FORCE with your drop command. But be careful that you are not dropping something with a similar name that is associated with something other than the index you are trying to create. Then try to create your new index again. Sometimes it can help to exit the session, then try again in another session. If all this fails, then you may just have to choose another name for your new index.

Although these two threads are from two different users, they have the same i.p. address and appear to be the same problem, so I merged them, but didn't delete either of them.

[Updated on: Thu, 18 December 2008 00:32]

Report message to a moderator

Re: getting error ora-29855 AND Error while creating Index on clob data (2 threads merged by bb) [message #376608 is a reply to message #376574] Thu, 18 December 2008 00:36 Go to previous messageGo to next message
asksrikanth@gmail.com
Messages: 9
Registered: December 2008
Location: Bangalore
Junior Member
Thank you very much, after renaming the index names the script worked properly.

When i run the query i got some 6 lines where some are tables and some are indexes.

One more question
i would like to know what the DR$....$ table will contain and its usage.

[Updated on: Thu, 18 December 2008 00:37]

Report message to a moderator

Re: getting error ora-29855 AND Error while creating Index on clob data (2 threads merged by bb) [message #376620 is a reply to message #376608] Thu, 18 December 2008 01:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9082
Registered: November 2002
Location: California, USA
Senior Member
When you create your_index, Oracle Text automatically creates several domain index tables with names like:

dr$your_index$i
dr$your_index$k
dr$your_index$n
dr$your_index$r
dr$your_index$x

When you run queries using CONTAINS, Oracle Text accesses these tables to obtain the results. The dr$your_index$i table contains the primary information about the tokens. The others are used to do things like link the rowid in the table to the token id in the domain index and to record inserts, updates, and deletes upon synchronization, pending optimization.

Report message to a moderator

Re: getting error ora-29855 AND Error while creating Index on clob data (2 threads merged by bb) [message #538080 is a reply to message #376620] Tue, 03 January 2012 16:13 Go to previous message
gwicke
Messages: 2
Registered: November 2006
Location: Indianapolis, IN USA
Junior Member
Barbara

I realize this a very late post to a very early solution you provided back in 2008 but I just wanted to thank you for the information.

I had the same issue and was easily able to find the 'DR$%' tables and indexes and when they were removed I was able to create the Text indexes successfully.

Thanks very much!!!

-gary

Report message to a moderator

Previous Topic: Oracle text - CONTAINS sql query returning wrong result
Next Topic: ordimageindex problem
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 03:02:30 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.