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
DEV_ODS_DATA datafile/dev_ods_data.878.1079624941 YES
select * from dba_tab_partitions where table_name='order';
all partitions compression is disabled
select iot_type,compression from user_tables where table_name='order';
When I trying to perform
ALTER TABLE order SHRINK SPACE compact;
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?