[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
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1061111/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1061111/