SEO

for unknown reason, one function-based index is disabled, what cause
this?
and which view I can use to check if it's enabled or disabled? the
"status" in
dba_indexes still show "valid" for this index.

niy38@hotmail.com wrote:
> for unknown reason, one function-based index is disabled, what cause
> this?
> and which view I can use to check if it's enabled or disabled? the
> "status" in
> dba_indexes still show "valid" for this index.

On what basis are you determining that the index is invalid?
What version of Oracle?

Look in all_ind_expressions, pull out the function, and try
it in SQL*Plus to see if it is the index or the function.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

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

niy38@hotmail.com wrote:
> 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

Before you think of anything else patch to 8.1.7.4. There is no
reason I can think of to be in 8.1.7.1.

Then look at all_indexes or dba_indexes.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

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 ***

He simply ask a simple question, function index's status.
Just check DBA_INDEXES.FUNCIDX_STATUS.

Maybe somebody did alter index disable, if he is hitting error, or used
rbo or some other reason, his index is not being used, and he thought
it was disabled.

Thanks chao_ping, funcidx_status is what I'm looking for.

If you recompile a function which makes the function based index it
will invalidate that index. With source control that is a tricky bit,
since every time is compiling is invalidating that index even when
nothing changed in the code.

Best is to drop and recreate it again, rebuilding it can cause some
other issues.

cybotto@yahoo.com wrote:
> If you recompile a function which makes the function based index it
> will invalidate that index. With source control that is a tricky bit,
> since every time is compiling is invalidating that index even when
> nothing changed in the code.
>
> Best is to drop and recreate it again, rebuilding it can cause some
> other issues.

Or use $ORACLE_HOME/rdbms/admin/utlrp.sql
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

actually the status is still valid, thought disabled.