[20131125]Partition, compress and drop column (ORA-39726).txt
http://www.dbaglobe.com/2013/11/partition-compress-and-drop-column-ora.html
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1.测试1:
drop table t1_compression purge;
create table t1_compression (id number, name varchar2(30)) partition by range (id) interval (100000) (partition p1 values less than (100000) ) compress;
insert into t1_compression select object_id,object_name from dba_objects where object_id is not null ;
commit;
col partition_name for a20
select partition_name,compression,compress_for from user_tab_partitions where table_name='T1_COMPRESSION';
PARTITION_NAME COMPRESS COMPRESS_FOR
-------------------- -------- ------------
P1 ENABLED BASIC
SYS_P3205 ENABLED BASIC
SYS_P3206 ENABLED BASIC
SCOTT@test> alter table t1_compression drop column name;
alter table t1_compression drop column name
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SCOTT@test> alter table t1_compression set unused column name;
Table altered.
SCOTT@test> desc t1_compression;
Name Null? Type
----- -------- --------
ID NUMBER
SCOTT@test> alter table t1_compression drop unused columns;
alter table t1_compression drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SCOTT@test> alter table t1_compression move partition p1 nocompress;
Table altered.
SCOTT@test> select partition_name,compression,compress_for from user_tab_partitions where table_name='T1_COMPRESSION';
PARTITION_NAME COMPRESS COMPRESS_FOR
-------------------- -------- ------------
P1 DISABLED
SYS_P3205 ENABLED BASIC
SYS_P3206 ENABLED BASIC
--仅仅P1分区取消了compress特性.
SCOTT@test> alter table t1_compression move partition SYS_P3205 nocompress;
Table altered.
SCOTT@test> select partition_name,compression,compress_for from user_tab_partitions where table_name='T1_COMPRESSION';
PARTITION_NAME COMPRESS COMPRESS_FOR
-------------------- -------- ------------
P1 DISABLED
SYS_P3205 DISABLED
SYS_P3206 ENABLED BASIC
alter table t1_compression move partition SYS_P3206 nocompress;
...
SCOTT@test> select partition_name,compression,compress_for from user_tab_partitions where table_name='T1_COMPRESSION';
PARTITION_NAME COMPRESS COMPRESS_FOR
-------------------- -------- ------------
P1 DISABLED
SYS_P3205 DISABLED
SYS_P3206 DISABLED
SCOTT@test> alter table t1_compression drop unused columns;
alter table t1_compression drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
--已经取消了压缩特性,依旧不能删除unused columns.
SCOTT@test> column column_name format a30
SCOTT@test> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column from user_tab_cols where table_name = 'T1_COMPRESSION';
COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME HID VIR
---------- ----------------- ------------------ ------------------------------ --- ---
2 2 SYS_C00002_13112515:04:52$ YES NO
1 1 1 ID NO NO
2.测试2:
drop table t1_compression purge;
create table t1_compression (id number, name varchar2(30)) partition by range (id) interval (100000) (partition p1 values less than (100000) ) compress for oltp;
insert into t1_compression select object_id,object_name from dba_objects where object_id is not null ;
commit;
SCOTT@test> select partition_name,compression,compress_for from user_tab_partitions where table_name='T1_COMPRESSION';
PARTITION_NAME COMPRESS COMPRESS_FOR
-------------------- -------- ------------
P1 ENABLED OLTP
SYS_P3207 ENABLED OLTP
SYS_P3208 ENABLED OLTP
SCOTT@test> alter table t1_compression drop column name;
Table altered.