[20131125]Partition, compress and drop column (ORA-39726).txt

[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.