CODEWITHSUNDEEP
oracle-databaseindexingpartitiondatastage
younes
younes

Reputation: 3

Unusable partition Oracle / datastage

I am facing an issue with my datastage job. I have to fill a table ttperiodeas in Oracle from a .csv file. The SQL query in Oracle connector is shown in this screenshot:

Oracle connector

And here is the oracle script

  CREATE TABLE TTPERIODEAS 
  ( 
      CDPARTITION VARCHAR2(5 BYTE) NOT NULL ENABLE,
      CDCOMPAGNIE NUMBER(4,0) NOT NULL ENABLE, 
      CDAPPLI NUMBER(4,0) NOT NULL ENABLE, 
      NUCONTRA CHAR(15 BYTE) NOT NULL ENABLE, 
      DTDEBAS NUMBER(8,0) NOT NULL ENABLE,
      DTFINAS NUMBER(8,0) NOT NULL ENABLE,
      TAUXAS NUMBER(8,5) NOT NULL ENABLE,

      CONSTRAINT PK_TTPERIODEAS 
          PRIMARY KEY (CDPARTITION, CDCOMPAGNIE, CDAPPLI, NUCONTRA, DTDEBAS)
)
PARTITION BY LIST(CDPARTITION)
(PARTITION  P_PERIODEAS_13Q VALUES ('13Q'));

When running the job, I get the following message error and the table is not filled.:

The index 'USINODSD0.SYS_C00249007' its partition is unusable

Please I need help thanks

Upvotes: 0

Views: 608

Answers (2)

William Robertson
William Robertson

Reputation: 16001

The index is global (i.e. not partitioned) because there is no using index local at the end of the definition. This is also true for the PK index shown above. (I'm assuming they are two different things, because by default the DDL above would create an index named PK_TTPERIODEAS, so I'm not sure what SYS_C00249007 is.) If you can drop and rebuild them as local indexes (i.e. partitioned to match the table) then truncating or dropping a partition will no longer invalidate indexes.

For example, you could rebuild the primary key as:

alter table ttperiodeas
    drop primary key;

alter table ttperiodeas 
    add constraint pk_ttperiodeas primary key (cdpartition,cdcompagnie,cdappli,nucontra,dtdebas)
    using index local;

I don't know how SYS_C00249007 is defined, but you could use something similar.

The create table command might be something like:

create table ttperiodeas 
( cdpartition  varchar2(5 byte) not null 
, cdcompagnie  number(4,0) not null 
, cdappli      number(4,0) not null 
, nucontra     varchar2(15 byte) not null 
, dtdebas      number(8,0) not null 
, dtfinas      number(8,0) not null 
, tauxas       number(8,5) not null 
, constraint pk_ttperiodeas
      primary key (cdpartition,cdcompagnie,cdappli,nucontra,dtdebas)
      using index local
)
partition by list(cdpartition)
( partition p_periodeas_13q values ('13Q') );

Alternatively, you could add the update global indexes clause when dropping the partition:

alter table demo_temp drop partition p_periodeas_14q update global indexes;

(By the way, NUCONTRA should probably be a standard VARCHAR2 and not CHAR, which is intended for cross-platform compatibility and ANSI completeness, and in practice just wastes space and creates bugs.)

Upvotes: 3

Martin Preiss
Martin Preiss

Reputation: 396

the message says that the index for the given partition is unusable: so you could try to rebuild the correponding index partition by the use of 

create index [index_name] rebuild partition [partition_name]

(with the fitting values for [index_name] and [partition_nme].

Before you do that you should check the status of the index partitions in user_indexes - since your error message looks not like Oracle error messages usually do.

But since the index is global as William Robertson pointed out, this is not applicable for the given situation.

Upvotes: 0

Related Questions