Usage Tracking- SYS_LOB* Indexes on LOGICAL_QUERIES and PHYSICAL_QUERIES

Question

We are facing one issue. We have UT in our OAC and DB is ADW. In LOGICAL_QUERIES and PHYSICAL_QUERIES tables Indexes are created on QUERY_BLOB columns. These are occupying lot of memory, please see below screenshot. Please suggest how can we delete there indexes without effecting data and performance. Auto Index Creation option is disables, and we did not create these, not sure how were these indexes created.

Subhakara Netala-Oracle · Answer

Thanks for the info. I will check on this.

Thanks

Gianni Ceresa · Answer

A search for that error gave this result from the Oracle forum:

Behind every LOB column the database creates a LOB data and a LOB index segment.

You can't drop them separately, but you need to drop the LOB COLUMN: the two segments will be dropped automatically.

You can't drop that index, because nobody created it (and it makes sense: I was trying to guess why somebody did add an index on that column and I couldn't find any logical reason).

The database works like that, you can't change it.

If space is a real issue, you should consider removing data from the UT table. Either deleting old rows, or creating an archive version of the table where you only bring in the info you care about and drop the LOBs.

Subhakara Netala-Oracle · Answer

Thanks @Gianni Ceresa . We have already tried dropping those indexes, but getting below error.