How defragment compressed partitioned table in oracle?

Question

I have partitioned table by day with compressed query high option for partitions in Oracle 11.2 database.

Delete by key and direct insert was used to populate a data for the table. So the table is fragmented currently.

As I know from Oracle documentation the shrink space can not be applied to compressed tables.

I performed next actions

alter table order nocompress;
alter table order enable row movement;
alter table order MODIFY PARTITION SYS_P21674395 nocompress;
....

Also checked the autoextensible for table space

select tablespace_name, file_name, autoextensible from dba_data_files
where tablespace_name='DEV_ODS_DATA'

DEV_ODS_DATA datafile/dev_ods_data.878.1079624941 YES

select * from dba_tab_partitions where table_name='order';

all partitions compression is disabled

also

select iot_type,compression from user_tables where table_name='order';

iot_type=NULL; compression=NULL

When I trying to perform

ALTER TABLE order SHRINK SPACE compact;

or

ALTER TABLE order MODIFY PARTITION SYS_P21674395 SHRINK SPACE;  

I get the next error

ALTER TABLE order MODIFY PARTITION SYS_P21674395 SHRINK SPACE
Error report -
ORA-10635: Invalid segment or tablespace type
10635. 00000 -  "Invalid segment or tablespace type"
*Cause:    Cannot shrink the segment because it is not in auto segment space
           managed tablespace or it is not a data, index or lob segment.
*Action:   Check the tablespace and segment type and reissue the statement

The table does not have FUNCTION based indexes

Do the exists approach to shrink space for the table without table recreating?

Answer 1

NOCOMPRESS does not decompress existing data in a table/partition, it applies only to new data inserted later. You can leave original compression method unchanged.

You can MOVE a partition:

ALTER TABLE order MOVE PARTITION SYS_P21674395 UPDATE INDEXES;

This is not a fully online operation, meaning the data in the partition can not be modified during the move.

If you were on version 12.1 or higher, you could use MOVE ONLINE:

ALTER TABLE order MOVE PARTITION SYS_P21674395 ONLINE UPDATE INDEXES;

MOVE will copy and recompress (given that the compression method was set) the partition then drop the old segment.

You can also specify the compression method in the MOVE command:

ALTER TABLE order MOVE PARTITION SYS_P21674395 
  COMPRESS FOR QUERY HIGH UPDATE INDEXES;

Answer 2

I found a solution

-- uncompress partition
  ALTER TABLE DEV_DW.TEST_TABLE move PARTITION SYS_P21822227 NOCOMPRESS UPDATE INDEXES;  

--required for shrink space
  ALTER TABLE TEST_TABLE enable row movement;

--Shrink the partition to reclaim unused space
ALTER TABLE TEST_TABLE MODIFY PARTITION SYS_P21822227 SHRINK SPACE;


--compress partition
ALTER TABLE DEV_DW.TEST_TABLE move PARTITION SYS_P21822227 COMPRESS FOR QUERY HIGH  UPDATE INDEXES; 

--gather stats
 BEGIN
DBMS_STATS.GATHER_TABLE_STATS('DEV_DW', 'TEST_TABLE',
                                     estimate_percent=>dbms_stats.auto_sample_size,
                                     block_sample=>TRUE,
                                     degree=>8) ;
END;
commit;

--test

  select 
  table_owner
  ,table_name
  ,partition_name
  ,high_value
  ,compression
  ,compress_for
  ,avg_row_len
  ,round(((blocks*16/1024)),2)/1024 "TOTAL_SIZE_GB" --The amount of space used by the partition in gigabytes.
  ,round((num_rows*avg_row_len/1024/1024),2)/1024 "ACTUAL_SIZE_GB" -- The amount of space used by the partition in gigabytes, calculated based on the number of rows and the average row length.
  ,round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 "FRAGMENTED_SPACE_GB" --The amount of space that is not used by the partition, in gigabytes.
  ,decode(round(((blocks*16/1024)),2),0,0, (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100) "percentage" --The percentage of unused space in the partition, calculated by dividing the unused space by the total space and multiplying by 100. 
from dba_tab_partitions 
WHERE 
1=1
and table_owner='DEV_DW'
and table_name='TEST_TABLE'
and partition_name='SYS_P21822227'
and round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 > 0
order by 6 desc
;