Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index.

How can I do something similar using Oracle? Is it possible?

  • What do you define as a full text index in Oracle? Are you talking about listing the Oracle Text indexes? – Justin Cave Jun 19 '15 at 21:06
  • Here on dba.stackexchange (DBA.SE), users are encouraged to show what they have attempted themselves. Have you explored the system catalogue? You have a rep. of 3.6k on StackOverflow, you should know this. – Vérace Jun 19 '15 at 21:06
  • @JustinCave Yes, probably. Full-text index is a SQL Server term, but I didn't know it was called differently in Oracle. – Doug Jun 22 '15 at 13:59
  • 2
    @Vérace I'm not very familiar with Oracle, but Google is my friend, and I did not post my question without some googling first. You took you time to look up my reputation just for the sake of being pedantic, maybe if you make better use of your time you can actually, you know, help people. – Doug Jun 22 '15 at 14:00

There are 4 types of Text indexes in Oracle, owned by the CTXSYS user (installed optionally), so you can simply query [DBA|ALL|USER]_INDEXES and filter rows by the ITYP_OWNER and ITYP_NAME columns:

  ityp_owner = 'CTXSYS'
  and ityp_name in ('CONTEXT', 'CTXCAT', 'CTXRULE', 'CTXXPATH')

Index types can be found in the DBA_INDEXTYPES view.

| improve this answer | |

I believe this will do the job but the databases I have access to at the moment do not have text indexes. I will confirm later today.

WHERE index_type = 'CONTEXT'
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.