Oracle index with I_JS$ in name

Question

After upgrade of Oracle to version 19 database started adding index named I_JS$XXXX on creation od materialized view XXXX

this created index blocks creation of our index on that field do anyoune know why oracle is creating this index ?

Answer 1

Probably, in your DB is "automatic indexing" turned on.

As variant of solution(local) of this query/problem:

   begin
     dbms_auto_index.drop_auto_indexes(
       owner          => 'YOUR_SCHEMA',
       index_name     => 'YOUR AUTO_INDEX',
       allow_recreate => false);
   end;
   /