Re: Index unusable rebuild

From: Tanel Poder <tanel_at_@peldik.com>
Date: Tue, 22 Jul 2003 22:28:21 +0300
Message-ID: <3f1d9053$1_1@news.estpak.ee>

Hi!

alter session set skip_unusable_indexes=true;

But it only works for indexes which aren't used by any primary key or unique constraint. You should disable constraints (possibly with keep index clause) to skip pk/uq constraint indexes.

Tanel.

"Charles J. Fisher" <cfisher_at_rhadmin.org> wrote in message news:Pine.BSO.4.53.0307221239150.28705_at_bart.rhadmin.org...
> I have several jobs which create some read-only tables:
>
> drop table t1;
> create table t1 NOLOGGING pctfree 0 as select...;
> create index i1 NOLOGGING pctfree 0...;
>
> I would like to truncate the tables and indexes rather than drop them (I
> would like to be able to make persistent changes to storage parameters). I
> would like these operations to be performed with nologging. I have tried:
>
> truncate table t1;
> alter index i1 unusable;
> insert /*+APPEND */ into t1 NOLOGGING select...;
> alter index i1 rebuild NOLOGGING;
>
> However, I am getting (something like) the following error:
>
> ERROR at line 1:
> ORA-26028: index I1 initially in unusable state
>
> How can I instruct Oracle to ignore an existing index during a direct-path
> insert? How can I establish a NOLOGGING operation for both the table and
> index rebuild?
>
> ----------------------------------------------------------------------

---

> / Charles J. Fisher |"Dig within. There lies the
spring /
> / cfisher_at_rhadmin.org | of good: ever dig, and it will
/
> / http://rhadmin.org | ever flow." -Marcus Aurelius
/
> --------------------------------------------------------------------------
- Received on Tue Jul 22 2003 - 14:28:21 CDT