Oracle Database Discussions

Guys,

I’m on 10.2.0.3.

Why I’m getting ORA-26027 during INSERT even when SKIP_UNUSABLE_INDEXES = TRUE. Is this a Bug or a Feature? What’s the solution/work-around here?

My goal here is to load data quicker using following steps. But I’m getting this error wherever there is UNIQUE index.

1) Make local index partitions unusable ( There are no global indexes )
2) Load data using DL INSERT
3) Rebuild UNUSABLE indexes.

Here is the test case…

SQL>drop table t1;

Table dropped.

SQL>create table t1
  2  PARTITION BY RANGE (CREATED)
  3  (
  4    PARTITION P1_2005 VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  5    PARTITION P1_2006 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  6    PARTITION P1_2007 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  7    PARTITION P1_2008 VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  8    PARTITION P1_MAX VALUES LESS THAN (MAXVALUE)
  9  )
 10  as
 11  select * from dba_objects
 12  /

Table created.

SQL>create unique index t1_p1 on t1 (created, object_id ) local;

Index created.

SQL>alter table t1 MODIFY PARTITION  p1_2006 unusable local indexes;

Table altered.

SQL>insert /*+ APPEND */ into t1 partition (p1_2006) select * from dba_objects where to_char(created, 'YYYY') = '2006';
insert /*+ APPEND */ into t1 partition (p1_2006) select * from dba_objects where to_char(created, 'YYYY') = '2006'
                          *
ERROR at line 1:
ORA-26027: unique index SCOTT.T1_P1 partition P1_2006 initially in unusable state

SQL>show parameter skip_un

skip_unusable_indexes           Boolean TRUE
SQL>