Re: Getting ORA-01502 even with SKIP_UNUSABLE_INDEXES = TRUE

<vafanassiev_at_aapt.com.au> wrote in message news:1102374430.559971.107770_at_z14g2000cwz.googlegroups.com...
> It should be possible to run SELECTS on
> a table with an index in UNUSABLE state if
> SKIP_UNUSABLE_INDEXES = TRUE (at the session level).
> At least if the index is (1) non-unique and
> (2) not used to enforce a constraint. However,
> I am still getting ORA-01502:
>
> Oracle 9.2.0.4.0 (32-bit) on Sun Solaris or Windows:
>
> SQL>
> SQL> create table test_table
> 2 (
> 3 n1 number,
> 4 n2 number,
> 5 n3 number
> 6 );
>
> Table created.
>
> SQL>
> SQL> create index test_table_idx1 on test_table(n1);
>
> Index created.
>
> SQL>
> SQL> select * from test_table where n1 = 1;
>
> no rows selected
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TABLE'
> 2 1 INDEX (RANGE SCAN) OF 'TEST_TABLE_IDX1' (NON-UNIQUE)
>
> SQL>
> SQL> alter index test_table_idx1 unusable;
>
> Index altered.
>
> SQL>
> SQL> select * from test_table where n1 = 1;
> select * from test_table where n1 = 1
> *
> ERROR at line 1:
> ORA-01502: index 'LPSROOT.TEST_TABLE_IDX1' or partition of such index
> is in
> unusable state
>
>
> SQL>
> SQL> alter session set skip_unusable_indexes = true;
>
> Session altered.
>
> SQL>
> SQL> select * from test_table where n1 = 1;
> select * from test_table where n1 = 1
> *
> ERROR at line 1:
> ORA-01502: index 'LPSROOT.TEST_TABLE_IDX1' or partition of such index
> is in
> unusable state
> -------------------------------------------------------------
> It works in 10g but not in earlier versions.
>

Well ... it was a bug in earlier versions. Try logging into metalink and search for Bug 2396261

Anurag Received on Mon Dec 06 2004 - 18:32:11 CST