What query returns the list of columns that are text indexed in Oracle?

Question

I need a SQL query that will return a list of all of the columns in an Oracle database which are full-text indexed, similar to the one for SQL Server given in answer How to find Full-text indexing on database in SQL Server 2008?.

Either a sample query, or a link to the documentation would be appreciated.

Answer 1

Text index information is stored in CTXSYS schema.

You get basic information using

SELECT * FROM CTXSYS.CTX_INDEXES;

Indexed column name is in column IDX_TEXT_NAME. But this information could be tricky since configuration of text index could be quite complex with mappings to multiple columns, sections etc. So IDX_TEXT_NAME contains only the column used in CREATE INDEX statement not necessarily the effective indexed columns.

When you define your own mapping the information will be available in CTXSYS.CTX_OBJECT_ATTRIBUTES view.