Compression failing for partition object
From: Lok P <loknath.73_at_gmail.com>
Date: Tue, 3 Aug 2021 21:24:24 +0530
Message-ID: <CAKna9VbrM6kkOH7AEjTA1LujDYvCnpYqkP5+QNtYh4N9YXbQBQ_at_mail.gmail.com>
Hi All, We got one scenario from the Dev, in which the compression of a composite partitioned index is giving error while doing in the subpartition granularity. I see a few blogs stating it has to be defined at partition level first with modify keyword but even that is also not working. Is this compress operation for individual partitions/subpartitions not allowed? And the only choice is to drop the whole index and then create it as compress enabled at one shot which is going to be resource intensive? Below is the sample test case which I tried below on a 19C database, and it failed.
)
, PARTITION P_TABLE VALUES ('TABLE')
( SUBPARTITION p_sw_low VALUES LESS THAN (1000) , SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
)
)
as select * from dba_objects where object_type in ('INDEX SUBPARTITION','TABLE') ; Create index USER1.CMPRS_IDx1 on
USER1.compression_test1(DECODE(object_id,NULL,1,NULL)) local parallel 4;
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Date: Tue, 3 Aug 2021 21:24:24 +0530
Message-ID: <CAKna9VbrM6kkOH7AEjTA1LujDYvCnpYqkP5+QNtYh4N9YXbQBQ_at_mail.gmail.com>
Hi All, We got one scenario from the Dev, in which the compression of a composite partitioned index is giving error while doing in the subpartition granularity. I see a few blogs stating it has to be defined at partition level first with modify keyword but even that is also not working. Is this compress operation for individual partitions/subpartitions not allowed? And the only choice is to drop the whole index and then create it as compress enabled at one shot which is going to be resource intensive? Below is the sample test case which I tried below on a 19C database, and it failed.
CREATE TABLE USER1.compression_test1
PARTITION BY LIST (OBJECT_TYPE)
SUBPARTITION BY RANGE (DATA_OBJECT_ID)
( PARTITION P_INDX_SUBPART VALUES ('INDEX SUBPARTITION')
( SUBPARTITION p_nw_low VALUES LESS THAN (1000)
, SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
)
, PARTITION P_TABLE VALUES ('TABLE')
( SUBPARTITION p_sw_low VALUES LESS THAN (1000) , SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
)
)
as select * from dba_objects where object_type in ('INDEX SUBPARTITION','TABLE') ; Create index USER1.CMPRS_IDx1 on
USER1.compression_test1(DECODE(object_id,NULL,1,NULL)) local parallel 4;
alter index USER1.CMPRS_IDx1 rebuild subpartition p_nw_low compress;
ERROR at line 1:
ORA-14189: this physical attribute may not be specified for an index
subpartition
alter index USER1.CMPRS_IDx1 modify subpartition p_nw_low compress;
ERROR at line 1:
ORA-14193: invalid ALTER INDEX MODIFY SUBPARTITION option
alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress;
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first
alter index USER1.CMPRS_IDx1 modify compress;
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 03 2021 - 17:54:24 CEST