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
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
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: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00955: name is already used by an existing object

Regards
Michel
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.
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: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00955: name is already used by an existing object


Regards
Michel
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
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: 7973
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: 7973
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.


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
Previous Topic: Oracle text - CONTAINS sql query returning wrong result
Next Topic: ordimageindex problem
Goto Forum:
  


Current Time: Fri Aug 29 18:01:08 CDT 2014

Total time taken to generate the page: 0.14047 seconds