Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, swadesh.

Asked: April 25, 2017 - 1:24 pm UTC

Last updated: May 26, 2021 - 2:32 am UTC

Version: ORACLE 12C

Viewed 10K+ times! This question is

You Asked


Hi Tom,

I have a table 'F_CUST_TRANSACTION_LINE_ITEMS' with primary key.I want to load rows here 11 Billons through Informatica (SQLLOADER).

While loading completed first 5 Millon rows,I checked index(Primary key) status has been changed valid to unusable.Now I am not able to delete records

from that table,below error is showing.

-----------------------------------------------------
delete from F_CUST_TRANSACTION_LINE_ITEMS where sal=23
Error report:
SQL Error: ORA-01502: index 'C360_DWH_DEV.SYS_C007973' or partition of such index is in unusable state
01502. 00000 - "index '%s.%s' or partition of such index is in unusable state"
*Cause: An attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL
operation
*Action: DROP the specified index, or REBUILD the specified index, or
REBUILD the unusable index partition
----------------------------------------------------------------------
I have checked 'skip_unusable_indexes' value also set TRUE.But still not able to delete.Able to insert only.

Please tell how can I delete records with index unusable state..This is very important please provide your comments and solution.

Note:Index rebuild takes much time that reason want to avoid it.Near about 180 millon rows take 3 hours to rebuild.

Regards,
Swadesh Mondal




and Connor said...

I assume you're doing a *direct* mode load.

A *unique* index cannot as "totally unusable" as a normal index. For example

SQL> create table t as select object_id, owner, object_name, created
  2  from dba_objects
  3  where object_Id is not null;

Table created.

SQL>
SQL> create index ix on t ( object_id );  -- normal index

Index created.

SQL>
SQL> alter index ix unusable;

Index altered.

SQL>
SQL> delete from t where rownum <= 10;

10 rows deleted.

SQL>
SQL> insert into t values (0,'X','Y',sysdate);

1 row created.

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t as select object_id, owner, object_name, created
  2  from dba_objects
  3  where object_Id is not null;

Table created.

SQL>
SQL> create unique index ix on t ( object_id );  -- unique index

Index created.

SQL>
SQL> alter index ix unusable;

Index altered.

SQL>
SQL> delete from t where rownum <= 10;
delete from t where rownum <= 10
*
ERROR at line 1:
ORA-01502: index 'MCDONAC.IX' or partition of such index is in unusable state


SQL>
SQL> insert into t values (0,'X','Y',sysdate);
insert into t values (0,'X','Y',sysdate)
*
ERROR at line 1:
ORA-01502: index 'MCDONAC.IX' or partition of such index is in unusable state


SQL>
SQL>


If you want to load a *lot* of data into a table, then your options are

1) just load in conventional mode. Slow but leaves table totally available to users

2) load in direct mode. Disable constraints before, re-activate afterward.

3) use partitioning to load and then exchange data in.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

unusable index after PMO

Rajeshwaran, May 04, 2017 - 12:42 pm UTC

Team,

Was reading through this documentation.
http://docs.oracle.com/database/122/SQLRF/CREATE-INDEX.htm#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__BABCHJDH
<quote>
When an index, or some partitions or subpartitions of an index, are created UNUSABLE, no segment is allocated for the unusable object. The unusable index or index partition consumes no space in the database.
</quote>

How ever the quote holds good only for a LOCALLY partitioned index and not for a Globally partitioned index and non-partitioned index.
demo@ORA12C>
demo@ORA12C> create table t
  2  partition by range( created_dt )
  3  ( partition p2013 values less than
  4     ( to_date('01-jan-2014','dd-mon-yyyy') ) ,
  5   partition p2014 values less than
  6     ( to_date('01-jan-2015','dd-mon-yyyy') ) ,
  7   partition pmax values less than (maxvalue)         )
  8  as
  9  select a.* ,
 10     to_date('01-Jan-2013','dd-mon-yyyy') +
 11     mod(rownum,1825) created_dt
 12  from all_objects a ;

Table created.

demo@ORA12C>
demo@ORA12C> create index t_nonpart_idx on t(object_id);

Index created.

demo@ORA12C> create index t_globalpart_idx on t(object_id,owner)
  2  global partition by hash(object_id) (
  3     partition p1,
  4     partition p2 ) ;

Index created.

demo@ORA12C> create index t_local_idx on t(object_name) local;

Index created.

demo@ORA12C> column index_name format a20
demo@ORA12C> column partition_name format a10
demo@ORA12C> column segment_name format a20
demo@ORA12C>
demo@ORA12C>
demo@ORA12C> select index_name,status
  2  from user_indexes
  3  where index_name = 'T_NONPART_IDX';

INDEX_NAME           STATUS
-------------------- --------
T_NONPART_IDX        VALID

demo@ORA12C>
demo@ORA12C> select index_name,partition_name,status
  2  from user_ind_partitions
  3  where index_name in ('T_GLOBALPART_IDX',
  4    'T_LOCAL_IDX');

INDEX_NAME           PARTITION_ STATUS
-------------------- ---------- --------
T_GLOBALPART_IDX     P1         USABLE
T_GLOBALPART_IDX     P2         USABLE
T_LOCAL_IDX          P2013      USABLE
T_LOCAL_IDX          P2014      USABLE
T_LOCAL_IDX          PMAX       USABLE

demo@ORA12C>
demo@ORA12C> select segment_name,partition_name,extents
  2  from user_segments
  3  where segment_name in ( 'T_NONPART_IDX' ,
  4    'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
  5  order by segment_name,partition_name nulls first;

SEGMENT_NAME         PARTITION_    EXTENTS
-------------------- ---------- ----------
T_GLOBALPART_IDX     P1                 23
T_GLOBALPART_IDX     P2                 23
T_LOCAL_IDX          P2013              20
T_LOCAL_IDX          P2014              20
T_LOCAL_IDX          PMAX               23
T_NONPART_IDX                           17

6 rows selected.

demo@ORA12C> alter table t
  2  split partition pmax at
  3  ( to_date('01-jan-2016','dd-mon-yyyy') )
  4  into ( partition p2016, partition pmax);

Table altered.

demo@ORA12C> select index_name,status
  2  from user_indexes
  3  where index_name = 'T_NONPART_IDX';

INDEX_NAME           STATUS
-------------------- --------
T_NONPART_IDX        UNUSABLE

demo@ORA12C>
demo@ORA12C> select index_name,partition_name,status
  2  from user_ind_partitions
  3  where index_name in ('T_GLOBALPART_IDX',
  4    'T_LOCAL_IDX');

INDEX_NAME           PARTITION_ STATUS
-------------------- ---------- --------
T_GLOBALPART_IDX     P1         UNUSABLE
T_GLOBALPART_IDX     P2         UNUSABLE
T_LOCAL_IDX          P2013      USABLE
T_LOCAL_IDX          P2014      USABLE
T_LOCAL_IDX          P2016      UNUSABLE
T_LOCAL_IDX          PMAX       UNUSABLE

6 rows selected.

demo@ORA12C> select segment_name,partition_name,extents
  2  from user_segments
  3  where segment_name in ( 'T_NONPART_IDX' ,
  4    'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
  5  order by segment_name,partition_name nulls first;

SEGMENT_NAME         PARTITION_    EXTENTS
-------------------- ---------- ----------
T_GLOBALPART_IDX     P1                 23
T_GLOBALPART_IDX     P2                 23
T_LOCAL_IDX          P2013              20
T_LOCAL_IDX          P2014              20
T_NONPART_IDX                           17

demo@ORA12C>

Could you help me to understand why this unused segments are not getting release in case of a Globally partitioned index and non-partitioned index
Connor McDonald
December 27, 2017 - 3:25 am UTC

"When an index, or some partitions or subpartitions of an index, are created UNUSABLE,"

any update to the above question

Rajeshwaran, Jeyabal, May 23, 2017 - 4:28 am UTC

Team - did i missing anything above? are you looking for more information, that i could help here ?
Connor McDonald
May 24, 2017 - 12:35 am UTC

Adding reviews to an existing review does not make it get answered faster. In fact...its more likely to push down to the bottom of the list :-)

partial indexing

Rajeshwaran, jeyabal, September 29, 2020 - 11:42 am UTC

Team:

we are seeing this behavior in our 18.10 database. Is this a right one?

to me it looks like "Truncate table" shouldn't turn "unusable" index to "usable" - kindly let us know your input on this.

demo@PDB1> create table t1
2 partition by range( x )
3 ( partition p1 values less than (1000) ,
4 partition p2 values less than (2000) indexing off )
5 as
6 select rownum as x, object_name as y
7 from all_objects
8 where rownum < 2000;

Table created.

demo@PDB1> create index t1_idx on t1(y) local indexing partial;

Index created.

demo@PDB1> col partition_name for a10
demo@PDB1> select partition_name, status
2 from user_ind_partitions
3 where index_name ='T1_IDX';

PARTITION_ STATUS
---------- --------
P1    USABLE
P2    UNUSABLE

demo@PDB1> truncate table t1;

Table truncated.

demo@PDB1> select partition_name, status
2 from user_ind_partitions
3 where index_name ='T1_IDX';

PARTITION_ STATUS
---------- --------
P1    USABLE
P2    USABLE

demo@PDB1>

Connor McDonald
September 30, 2020 - 2:29 am UTC

Known bug 28749619

The workaround is to be careful :-) or truncate at partition level

unuable index and datapump Imports

Rajeshwaran Jeyabal, May 21, 2021 - 7:06 am UTC 

demo@XEPDB1> create table t as select * from all_objects;

Table created.

demo@XEPDB1> $ expdp demo/demo@pdb1 directory=DEMO_DIR dumpfile=t.dmp logfile=t.log tables=T

Export: Release 18.0.0.0.0 - Production on Fri May 21 12:02:52 2021
Version 18.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Starting "DEMO"."SYS_EXPORT_TABLE_01":  demo/********@pdb1 directory=DEMO_DIR dumpfile=t.dmp logfile=t.log tables=T
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEMO"."T"                                  9.127 MB   68392 rows
Master table "DEMO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_TABLE_01 is:
  D:\TRASH\T.DMP
Job "DEMO"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 21 12:03:36 2021 elapsed 0 00:00:41

demo@XEPDB1> drop table t purge;

Table dropped.

demo@XEPDB1> create table t
  2  nologging
  3  partition by range( created )
  4  interval( numtoyminterval(1,'month'))
  5  ( partition p2010 values less than ( to_date('01-Jan-2011','dd-mon-yyyy') ) )
  6  as
  7  select * from all_objects
  8  where 1 = 0 ;

Table created.

demo@XEPDB1> create index t_idx on t( owner,object_type ) local unusable;

Index created.

demo@XEPDB1> col partition_name for a10
demo@XEPDB1> select partition_name,status
  2  from user_ind_partitions
  3  where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P2010      UNUSABLE

demo@XEPDB1> $ impdp demo/demo@pdb1 directory=DEMO_DIR dumpfile=t.dmp logfile=t.log  content=data_only

Import: Release 18.0.0.0.0 - Production on Fri May 21 12:05:07 2021
Version 18.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  demo/********@pdb1 directory=DEMO_DIR dumpfile=t.dmp logfile=t.log content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."T"                                  9.127 MB   68392 rows
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at Fri May 21 12:05:29 2021 elapsed 0 00:00:20

demo@XEPDB1> select partition_name,status
  2  from user_ind_partitions
  3  where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P2010      UNUSABLE
SYS_P888   USABLE
SYS_P889   USABLE
SYS_P890   USABLE

demo@XEPDB1>


Team:

In the above test case, though we got local index set to "unusable" - upon data loading, for the newly created partitions they got indexes in "usable" state,
so what options,we can use to make this index "unusable" for the newly created partitions ?
so that we can rebuild the partition concurrently using this API "dbms_pclxutil" during off the business hours and dont want the index to be maintained during datapump imports

Connor McDonald
May 26, 2021 - 2:32 am UTC

This is not a datapump issue but a loading issue - an empty partition gets a fresh segment and hence a valid index

SQL> create table t
  2      partition by range( created )
  3      interval( numtoyminterval(1,'month'))
  4      ( partition p2010 values less than ( to_date('01-Jan-2011','dd-mon-yyyy') ) )
  5      as
  6      select * from all_objects
  7      where 1 = 0 ;

Table created.

SQL> create index t_idx on t( owner,object_type ) local unusable;

Index created.

SQL> insert /*+ APPEND */ into t
  2  select * from all_objects;

77436 rows created.

SQL> commit;

Commit complete.

SQL> col partition_name for a10
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P2010      UNUSABLE
SYS_P18386 USABLE
SYS_P18387 USABLE
SYS_P18388 USABLE
SYS_P18389 USABLE
SYS_P18390 USABLE
SYS_P18391 USABLE
SYS_P18392 USABLE
SYS_P18393 USABLE
SYS_P18394 USABLE
SYS_P18395 USABLE
SYS_P18396 USABLE
SYS_P18397 USABLE
SYS_P18398 USABLE
SYS_P18399 USABLE
SYS_P18400 USABLE
SYS_P18401 USABLE
SYS_P18402 USABLE
SYS_P18403 USABLE
SYS_P18404 USABLE
SYS_P18405 USABLE
SYS_P18406 USABLE

22 rows selected.


You;ll be better off creating the index after the load.

I'll ask around internally for what other options are possible

=============
Addenda

You can get around this with partial indexing, eg

SQL> create table t ( x int, y int )
  2      partition by range ( x )
  3      interval ( 10 )
  4      (
  5        partition p1 values less than ( 2 )
  6      ) indexing off;

Table created.

SQL>
SQL> create index t_idx on t ( x ) local unusable indexing partial;

Index created.

SQL>
SQL> col partition_name for a10
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE

1 row selected.

SQL> insert into t values (4,4);

1 row created.

SQL> insert into t values (40,40);

1 row created.

SQL> col partition_name for a10
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE
SYS_P18439 UNUSABLE
SYS_P18440 UNUSABLE

3 rows selected.


then set indexing back to full and do your rebuild as you like
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.