After mass structure updates, some views/procedures became invalid.

So I just recompiled them with:

exec dbms_utility.compile_schema( USER )

But after that, all function-based indexes, which before were in a good state, became broken, and SQL statements against those tables failed. And that is the problem.

Example of such indexes:



Why did that happen?

  • You should only recompile invalid objects. Otherwise Oracle thinks your function's body has changed. And then how is Oracle supposed to know that your function Str2Number doesn't all of a sudden return different values? Apr 29, 2013 at 8:09

1 Answer 1


Oracle is compiling all objects, so as far as Oracle is concerned all code in all objects is new code.

As a consequence, Oracle has no way of knowing that your function Str2Number still returns the same values that were used when the index was created.

Instead you should only recompile invalid objects,

exec dbms_utility.compile_schema(user, false);

will do this.

  • this really helps. thanx. but it is unfair from oracle 1. befor compile_schema indexes was ok and than become broken 2. if it recompiles ALL objects, why it skips function-based indexes 3. no warnings about ... just screams from end-point users :(
    – evg345
    Apr 29, 2013 at 8:34
  • An index isn't a compiled object like a stored procedure. You (re)build an index, not (re)compile it. Apr 29, 2013 at 8:37
  • I do also wonder if TO_NUMBER has been overlooked
    – Philᵀᴹ
    Apr 29, 2013 at 8:45
  • Possibly, but in general for a user-defined function used in a function-based index the above holds. Apr 29, 2013 at 8:47
  • 1
    Seems like a good reason to try to avoid using user-defined functions in function-based indexes if they can be avoided. Str2Number doesn't sound like it brings much to the party that to_number wouldn't. Apr 30, 2013 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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