Re: Compression failing for partition object

Actually as i had already posted, i had tried that option but getting below error. Not sure why.

alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress;

ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first

On Wed, 4 Aug 2021, 1:45 am Pap, <oracle.developer35_at_gmail.com> wrote:

> Below blog shows a similar example(though its range-hash but not exactly
> like your list-range example) but it seems the subpartition level rebuild
> worked after setting/modifying the compression property at partition level.
> Have you tried that way?
>
> https://raajeshwaran.blogspot.com/2021/02/?m=0
>
> On Tue, Aug 3, 2021 at 9:24 PM Lok P <loknath.73_at_gmail.com> wrote:
>
>> 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-l