Re: disabled function-based index

On Fri, 07 Oct 2005 16:47:27 -0700, niy38 interested us by writing:

> the status of index is still valid.
>
> I have form running error with
> the index is disabled, after I alter index xxxx enable, everything
> is just fine.
>
> I'd like to know which view I can see the index disabled or enabled.
>
> version is 8.1.7.1

Normally all such information about an index is kept in ???_INDEXES where [??? IN (DBA, ALL, USER)] as described in the Oracle Database Reference manual for the version you are using. Yes, the document exists for 8.1.7, and yes it does describe the view.

In this case, though, you would be advised to review the Oracle Concepts manual in the section describing Function Based Indexes. The relevant section is in Chapter 10, and I quote the most important part:

"
Dependencies of Function-Based Indexes

 Function-based indexes depend on the function used in the expression that  defines the index. If the function is a PL/SQL function or package  function, the index will be disabled by any changes to the function  specification.

 PL/SQL functions used in defining function-based indexes must be  DETERMINISTIC. The index owner needs the EXECUTE privilege on the  defining function. If the EXECUTE privilege is revoked, then the  function-based index is marked DISABLED. "

so your answer in this case actually resides with DBA_DEPENDENCIES as well as the dicionary views related to object privileges.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***