How to mark bitmap index unusable?

[janlars_goedtke]

Hello,

we have problems with bitmap index like in Note 2017734 ORA-14646 and ORA-14400 on BW load run:

INSERT INTO "/BIC/FZIC_C04" VALUES(:A0 ,:A1 ,:A2 ,:A3 ,:A4 ,:A5 ,:A6 ,:A7 ,:A8 ,:A9 ,:A10 ,:A11 ,:A1\

aborts with

B  ***LOG BY4=> sql error 14400  performing INS on table /BIC/FZIC_C04                  [dbtran       7634]

B  ***LOG BY0=> ORA-14400: inserted partition key does not map to any partition [dbtran       7634]

We rebuild the indexes via RSA like stated in the note, no change. So we we opened a message in which is stated:

A)Drop any bitmap indexes defined on the table, and re-create them on

the operation is complete.

B)Mark all index fragments of all bitmap indexes defined on the table

UNUSABLE and rebuild them once the operation is complete.

It's not stated how, only a link to

and

Compress individual Table Partition with local ... | Oracle Community

Thats ok, but we don't know how to build the statement and SAP is unwilling to tell us.

Any help appreciated

thanks

Jan

[janlars_goedtke · Accepted solution]

Hi,

thanks for your advise.

I'm not sure what the initial problem is.

We reproduced it and sometimes it Comes along with ORA-14400, sometimes with ORA-14646 and sometimes with both:

C  Dump statement cache:
C  sc_p=0x53f7078,no=13,idc_p=(nil),con=0,act=0,slen=121,smax=256,#vars=0,stmt=0x5613b30,table=
C  ALTER TABLE "/BIC/FZCS_C04" ADD PARTITION "/BIC/FZCS_C040000001523" VALUES LESS THAN  (0000001523 ) \
C  TABLESPACE "PSAPSR3" ;
C  dbsl err=99=DBSL_ERR_DB -> dbds err=1=DS_SQLERR, dbdsoci.c:937
C  *** ERROR => ^^ Ds_exec_imm() -> err=1=DS_SQLERR
[dbdsoci.c    949]
C  {root-id=005056B21CDC1ED493F298C47ED053A0}_{conn-id=543674D75E0EA9E8E1000000AC107486}_1

B  ***LOG BY2=> sql error 14646      performing EXE        [dbds         578]
B  ***LOG BY0=> ORA-14646: Specified alter table operation involving compression cannot be performed in the presence of usable bitmap indexes [dbds         578]
B  ***LOG BY1=> sql error 14646      [dbacds       1512]

Jan

[stefan_koehler · Accepted solution]

Hi Jan,

this is a completey different issue and works as desgined (based on your settings).

I have already posted a test case on SCN to demonstrate this issue ( ).

This error occurs, if you have enabled OLTP compression on tablespace level, but still have some used / uncompressed / partitioned tables in it and the corresponding bitmap indexes are not dropped while running a DDL (like add partition) on the base object.

Regards

Stefan

[janlars_goedtke · Accepted solution]

Hi Stefan,

I'm just missing the point how to identify the table / index and the partion for the 'unusable' statement.

The other solution would be to 'decompress' the the tbale?

Thanks

Jan

[stefan_koehler · Accepted solution]

Hi Jan,

>> The other solution would be to 'decompress' the the tbale?

No, please check my demo again. It seems like somebody has altered the default tablespace setting (COMPRESS FOR ALL OPERATIONS) for PSAPSR3 and have not checked the partitioned tables with existing bitmaps within. This is a pretty common mistake.

You can just reset the default setting again and "do it right" afterwards. Your BI chains will run fine and you can plan the OLTP compression unhurriedly while the system is running.

Regards

Stefan

[janlars_goedtke · Accepted solution]

Hi,

i assume the following:

The system was uncompressed, it was exported and then imported with compression and the whole SMIGR_CREATE_DDL thing.

What so you mean with " reset the default setting again" and "do it right)" (sorry, I'm not from the BI side) and will it have impact on existing indexes (as we have rebuiild many of them).

Thanks

Jan

[stefan_koehler · Accepted solution]

Hi Jan,

>> will it have impact on existing indexes (as we have rebuiild many of them).

No, please forget all about that index stuff as it has nothing to do with your issue. The error ORA-14400 is just a follow up error from ORA-14646 as Oracle tried to add a partition to the table (which failed due to that OLTP compression setting and the uncompressed partitioned table with existing bitmap indexes) and then insert some data, but was not able to.

Just execute the following SQL and then hand over this issue to your Oracle DBA to implement Oracle Advanced compression the right way (and not just by setting the default tablespace attribute).

SQL> alter tablespace PSAPSR3 default NOCOMPRESS;

Regards

Stefan

[janlars_goedtke · Accepted solution]

After doing so, it is sufficient to recreate the index via RSA1?

[stefan_koehler · Accepted solution]

Hi Jan,

yes, if you have already modified the index in some way - however this task was unnecessary.

Regards

Stefan

[janlars_goedtke · Accepted solution]

Hi,

solved it, thanks for your patience.

Jan

[fidel_vales]

Hi,

I think you have an understanding problem.

You mention that your error is ORA-14400

what is the meaning of that error?

From the oracle documentation

ORA-14400: inserted partition key does not map to any partition
Cause: An attempt was made to insert a record into, a Range or Composite Range object, with a concatenated partition key that is beyond the concatenated partition bound list of the last partition -OR- An attempt was made to insert a record into a List object with a partition key that did not match the literal values specified for any of the partitions.
Action: Do not insert the key. Or, add a partition capable of accepting the key, Or add values matching the key to a partition specification

before doing any thing, I'd say that you are trying to insert into a partition that do not exist.

Maybe you need a partition to put that value

May be the problem is not the index

[ACE-SAP]

Hi

Have you check note 1842044 - ORACLE: dataload without dropping bitmap indexes ?

It is providing an solution to avoid updating index during dataload.

I can give you the SQL code to delete / create an index (using package DBMS_METADATA.GET_DDL)

and you can code it in ABAP Native SQL but I'm not sure it's what you're looking for.

Regards

[former_member188883]

Hi Jan,

Could you try this  on SQL prompt

alter index <indexname> unusable;

Regards,

Deepak Kori

[janlars_goedtke]

Hi,

are the Indexes in SE14 or SE11 the Bitmap Indexes?

e.g. "/BIC/FZIC_C04~10, "/BIC/FZIC_C04~20 etc?

Jan