OraFAQ Forum: Text & interMedia » Oracle Text Search - How to drop DR$ tables and indexes

Home » Server Options » Text & interMedia » Oracle Text Search - How to drop DR$ tables and indexes (Oracle DB, 19.0.0.0.0, Windows 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Text Search - How to drop DR$ tables and indexes [message #686843]

Tue, 24 January 2023 23:22

OraDev16
Messages: 8
Registered: October 2021

Junior Member

Hi All,
I'm using Oracle Text Search. For some reason I need to drop all the DR$ tables and indexes which have been created automatically when I created the context indexes. I'm getting the following error messages when I try to delete the objects :

Table - ORA-00942 : table or view does not exist.
Index - ORA-01418: specified index does not exist.

Thanks

Report message to a moderator

Re: Oracle Text Search - How to drop DR$ tables and indexes [message #686844 is a reply to message #686843]

Wed, 25 January 2023 01:37

John Watson
Messages: 8944
Registered: January 2010
Location: Global Village

Senior Member

orclz> create index ctxi on emp(ename) indextype is ctxsys.context;

Index created.

orclz> select object_name,object_type from user_objects order by created;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
DEPT                           TABLE
PK_DEPT                        INDEX
EMP                            TABLE
PK_EMP                         INDEX
BONUS                          TABLE
SALGRADE                       TABLE
DR$CTXI$I                      TABLE
CTXI                           INDEX
SYS_IL0000078879C00006$$       INDEX
SYS_C0012383                   INDEX
DR$CTXI$U                      TABLE
SYS_LOB0000078879C00006$$      LOB
DR$CTXI$K                      TABLE
DR$CTXI$N                      TABLE
SYS_IOT_TOP_78883              INDEX
DR$CTXI$KD                     INDEX
DR$CTXI$X                      INDEX
DR$CTXI$KR                     INDEX

18 rows selected.

orclz> drop index ctxi;

Index dropped.

orclz> select object_name,object_type from user_objects order by created;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
PK_DEPT                        INDEX
DEPT                           TABLE
SALGRADE                       TABLE
PK_EMP                         INDEX
BONUS                          TABLE
EMP                            TABLE

6 rows selected.

orclz>

Report message to a moderator

Re: Oracle Text Search - How to drop DR$ tables and indexes [message #686847 is a reply to message #686843]

Wed, 25 January 2023 03:55

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

As demonstrated by John Watson, the dr$... tables are created automatically when you create the context index and dropped automatically when you drop the context index. They are domain index tables. They are your index. Your index will not work without them. So, you should not be trying to delete them.

Report message to a moderator

Previous Topic:	Materialized View with Context Index
Next Topic:	How to determine last tiime Content was indexed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 07 19:47:07 CDT 2024