SQL> create table dbarefpart_t1 ( cyear number, OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(128), SUBOBJECT_NAME VARCHAR2(30), OBJECT_ID NUMBER ) partition by range (cyear) ( partition p2010 values less than (2011), partition p2011 values less than (2012)); Table created. SQL> insert into dbarefpart_t1 select '2010', owner, object_name, subobject_name, object_id from dba_objects; | Case 1: without indexes, you can exchange compressed standalone table into a non-compressed table. SQL> select table_name, partitioned, compression, compress_for from dba_tables where table_name = 'DBAREFPART_T1'; TABLE_NAME PAR COMPRESS COMPRESS_FOR ------------------------------ --- -------- ------------ DBAREFPART_T1 YES SQL> SQL> select table_name, partition_name, compression, compress_for from dba_tab_partitions where table_name ='DBAREFPART_T1'; TABLE_NAME PARTITION_NAME COMPRESS ------------------------------ ------------------------------ -------- COMPRESS_FOR ------------ DBAREFPART_T1 P2010 DISABLED DBAREFPART_T1 P2011 DISABLED SQL> SQL> create table dbarefpart_t1_comp compress for oltp as select * from dbarefpart_t1 partition (p2010) order by owner, object_name; TABLE_NAME PAR COMPRESS COMPRESS_FOR ------------------------------ --- -------- ------------ DBAREFPART_T1_COMP NO ENABLED OLTP SQL> alter table dbarefpart_t1 exchange partition p2010 with table dbarefpart_t1_comp with validation; Table altered. SQL> select table_name, partitioned, compression, compress_for from dba_tables where table_name = 'DBAREFPART_T1_COMP'; TABLE_NAME PAR COMPRESS COMPRESS_FOR ------------------------------ --- -------- ------------ DBAREFPART_T1_COMP NO DISABLED SQL> select table_name, partition_name, compression, compress_for from dba_tab_partitions where table_name = 'DBAREFPART_T1'; TABLE_NAME PARTITION_NAME COMPRESS ------------------------------ ------------------------------ -------- COMPRESS_FOR ------------ DBAREFPART_T1 P2010 ENABLED OLTP DBAREFPART_T1 P2011 DISABLED SQL> You can exchange a compressed partition out with non-compressed table. SQL> alter table dbarefpart_t1 exchange partition p2010 with table dbarefpart_t1_comp with validation; Table altered. SQL> Case 2: Partition exchange fails if one of exchange partner has compression indexes and the other does not. SQL> create index idx_DBAREFPART_t1_first3 on DBAREFPART_t1 (cyear, owner, OBJECT_NAME) local; Index created. SQL> select index_name, partition_name, compression from dba_ind_partitions where index_name = 'IDX_DBAREFPART_T1_FIRST3'; INDEX_NAME PARTITION_NAME COMPRESS ------------------------------ ------------------------------ -------- IDX_DBAREFPART_T1_FIRST3 P2010 DISABLED IDX_DBAREFPART_T1_FIRST3 P2011 DISABLED SQL> create index idx_DBAREFPART_t1_comp_first3 on DBAREFPART_t1_comp (cyear, owner, OBJECT_NAME) compress 1; Index created. SQL> select index_name, compression, prefix_length from dba_indexes where index_name = 'IDX_DBAREFPART_T1_COMP_FIRST3'; INDEX_NAME COMPRESS PREFIX_LENGTH ------------------------------ -------- ------------- IDX_DBAREFPART_T1_COMP_FIRST3 ENABLED 1 SQL> SQL> alter table DBAREFPART_t1 exchange partition p2010 with table DBAREFPART_t1_comp including indexes with validation; drop index idx_DBAREFPART_t1_first3; drop index idx_DBAREFPART_t1_comp_first3; alter table DBAREFPART_t1 exchange partition p2010 with table DBAREFPART_t1_comp including indexes with validation Case 4: Only both indexes are compressed, partition exchange “including indexes with validation” would succeed. SQL> drop index idx_DBAREFPART_t1_first3;Index dropped. SQL> create index idx_DBAREFPART_t1_first3 on DBAREFPART_t1 (cyear, owner, OBJECT_NAME) compress 1 local; Index created. SQL> select index_name, compression, prefix_length from dba_indexes where index_name = 'IDX_DBAREFPART_T1_FIRST3'; INDEX_NAME COMPRESS PREFIX_LENGTH ------------------------------ -------- ------------- IDX_DBAREFPART_T1_FIRST3 ENABLED 1 SQL> SQL> drop index idx_DBAREFPART_t1_comp_first3; create index idx_DBAREFPART_t1_comp_first3 on DBAREFPART_t1_comp (cyear, owner, OBJECT_NAME) compress 1; alter table DBAREFPART_t1 exchange partition p2010 with table DBAREFPART_t1_comp including indexes with validation; SQL> Index dropped. SQL> SQL> Index created. SQL> SQL> Table altered. SQL> SQL> SQL> |