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

http://www.dbaglobe.com/2013/11/partition-compress-and-drop-column-ora.html

在12c下测试看看,结果如何?

SCOTT@ztest> @ver
BANNER                                                                              CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@ztest> select max(object_id),min(data_object_id) from dba_objects;
MAX(OBJECT_ID) MIN(DATA_OBJECT_ID)
-------------- -------------------
         92011                   0

drop table t1_compression purge;
create table t1_compression (id number, name varchar2(30)) partition by range (id) interval (30000) (partition p1 values less than (30000) ) 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_P311             ENABLED  BASIC
SYS_P312             ENABLED  BASIC
SYS_P313             ENABLED  BASIC

SCOTT@ztest> 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@ztest> alter table t1_compression set unused column name;
Table altered.

SCOTT@ztest> desc t1_compression;
Name  Null?    Type
----- -------- -----------------
ID             NUMBER

SCOTT@ztest> 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@ztest> alter table t1_compression move partition p1 nocompress;

Table altered.

SCOTT@ztest> select partition_name,compression,compress_for from user_tab_partitions where table_name='T1_COMPRESSION';
PARTITION_NAME       COMPRESS COMPRESS_FOR
-------------------- -------- ------------------------------
P1                   DISABLED
SYS_P311             ENABLED  BASIC
SYS_P312             ENABLED  BASIC
SYS_P313             ENABLED  BASIC

SCOTT@ztest> alter table t1_compression move partition SYS_P311 nocompress;
Table altered.

SCOTT@ztest> alter table t1_compression move partition SYS_P312 nocompress;
Table altered.

SCOTT@ztest> alter table t1_compression move partition SYS_P313 nocompress;
Table altered.

SCOTT@ztest> select partition_name,compression,compress_for from user_tab_partitions where table_name='T1_COMPRESSION';
PARTITION_NAME       COMPRESS COMPRESS_FOR
-------------------- -------- ------------------------------
P1                   DISABLED
SYS_P311             DISABLED
SYS_P312             DISABLED
SYS_P313             DISABLED

SCOTT@ztest> alter table t1_compression drop unused columns;
Table altered.

--看来11G下有bug.

SCOTT@ztest> column column_name format a30
SCOTT@ztest> 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
---------- ----------------- ------------------ ------------------------------ --- ---
         1                 1                  1 ID                             NO  NO