Home » Server Options » Text & interMedia » Oracle Text Search - How to drop DR$ tables and indexes (Oracle DB, 19.0.0.0.0, Windows 10)
Oracle Text Search - How to drop DR$ tables and indexes [message #686843] Tue, 24 January 2023 23:22 Go to next message
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
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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>
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 Go to previous message
Barbara Boehmer
Messages: 9077
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.
Previous Topic: Materialized View with Context Index
Next Topic: How to determine last tiime Content was indexed
Goto Forum:
  


Current Time: Fri Mar 29 00:21:18 CDT 2024