ORA-14646 during oracle compression in BW system

[Former Member]

Hi Experts,

we are doing oracle advanced compression in BW 7.0 system with oracle

11g.

I have created new tablespace with compression enabled. After

reorg&compression tables and indexes are transferred to new tablespace

which has compression enabled.

Now in BI system new indexes are created during loads, if it happens to be a bitmap index , then system tries to

create it as compressed index in new tablespace but fails with

following

error.

Database error 14646 at EXE

> ORA-14646: Specified alter table operation involving

> compression cannot be performed in the presence of usable

> bitmap indexes

Does it mean that bitmap indexes need to be put in separate tablespace

with compression disabled ??

thanks,

[stefan_koehler · Accepted solution]

Hi Daljit,

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. You will get this error, if SAP tries to add a new partition to such an object in a BI process chain.

Here is just a short demo case of it.

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, 
                       SEGMENT_SPACE_MANAGEMENT, COMPRESS_FOR 
            from dba_tablespaces;
TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN COMPRESS_FOR
------------------------------ ---------- --------- ------ ------------
....
TEST01                         LOCAL      SYSTEM    AUTO
....
SQL> create table MYTEST
    (employee_id NUMBER(4) NOT NULL,
    last_name VARCHAR2(10),
    department_id NUMBER(2))
    PARTITION BY RANGE (department_id)
    (PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE TEST01,
     PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE TEST01,
     PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE TEST01)
    TABLESPACE TEST01;
SQL> insert into MYTEST values (1,'Test',2);
SQL> insert into MYTEST values (24 ,'Test',2);
SQL> insert into MYTEST values (35,'Test',2);
SQL> commit;
SQL> create bitmap index MYTESTI on MYTEST(employee_id) 
           local TABLESPACE TEST01;
SQL> alter tablespace TEST01 default COMPRESS FOR ALL OPERATIONS;
SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, 
                       SEGMENT_SPACE_MANAGEMENT, COMPRESS_FOR 
           from dba_tablespaces;
TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN COMPRESS_FOR
------------------------------ ---------- --------- ------ ------------
....
TEST01                         LOCAL      SYSTEM    AUTO   OLTP
....
SQL> alter table MYTEST add partition employees_part4 values less than (50);
ORA-14646: Specified alter table operation involving compression cannot be performed 
in the presence of usable bitmap indexes

Solution: Reorganize (and compress) all the corresponding used partitioned tables, those bitmap indexes are not dropped (by BI process chains) while running DMLs (add partition) .. or just revoke the default compression setting on tablespace level.

Regards

Stefan

P.S.: @Deepak Kori: Just to google an ORA error without any clue about the issues is not very useful.

[Former Member · Accepted solution]

Stefan thanks for detailed reply, my best bet is revoking compression at tablespace level and putting everything in PSAPSID tablespace as before. Over period of time if new objects are created as uncompressed, I can check and compress them

thanks for detailed explanation,

daljit

[former_member188883]

Hi Daljit,

Please refer following link for ORA-14646

https://forums.oracle.com/thread/948599

Hope this helps.

Regards,

Deepak Kori