Sunday, November 14, 2010

Oracle compression table restrictions


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production



SQL> create table compress_table (id number, name varchar2(30)) compress basic;

Table created.

SQL> insert into compress_table select object_id,object_name from dba_objects;

17392 rows created.

SQL> commit;

Commit complete.
SQL> select table_name,pct_free from user_tables
2 order by pct_free;

TABLE_NAME PCT_FREE
------------------------------ ----------
COMPRESS_TABLE 0
DEPARTMENTS 10
TEST_CHAINED_TABLE 10
CHAINED_ROWS 10
T1 10
EMPLOYEES 10



SQL> alter table compress_table add (col3 varchar2(20));

Table altered.

SQL> alter table compress_table add (col4 varchar2(20) default 'col4');
alter table compress_table add (col4 varchar2(20) default 'col4')
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> alter table compress_table drop column col3;
alter table compress_table drop column col3
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> alter table compress_table nocompress;

Table altered.

SQL> alter table compress_table drop column col3;
alter table compress_table drop column col3
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables



SQL> alter table compress_table shrink space;
alter table compress_table shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type