This discussion is archived
1 Reply Latest reply: Dec 24, 2012 5:11 AM by robinsc RSS

Uncompress subpartition table after restoring exadata to non-exadata storag

Yoav Newbie
Currently Being Moderated
Hi,
One of my databases which is running on exadata x2-2 , has been restored to non-exadata machine in order to test few things.

I had a sub-partition table in the exadata , compressed for query high.

On test machine (NOT EXADATA) , after uncompressing this subpartiton tables i am getting the following err message :
 
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type 
I have executed the following commands :
 
alter table crm.cm_ncd modify partition P01_CM_NCD nocompress; 
alter table crm.cm_ncd modify partition P02_CM_NCD nocompress; 
alter table crm.cm_ncd modify partition P03_CM_NCD nocompress; 
alter table  crm.cm_ncd nocompress; 
Checking table :
 
SQL> select table_name,compression,compress_for 
  2  from dba_tables where table_name='CM_NCD'; 

TABLE_NAME                     COMPRESS COMPRESS_FOR 
------------------------------ -------- ------------ 
CM_NCD 
checking partition:
 
SQL> select table_name,partition_name,compression 
  2  from dba_tab_partitions 
  3  where table_name='CM_NCD'; 

TABLE_NAME                     PARTITION_NAME                 COMPRESS 
------------------------------ ------------------------------ -------- 
CM_NCD                         P01_CM_NCD                     DISABLED 
CM_NCD                         P02_CM_NCD                     DISABLED 
CM_NCD                         P03_CM_NCD                     DISABLED 
checking sub-partition:
 
SQL> select table_name,partition_name,compression 
  2  from dba_tab_subpartitions 
  3  where table_name='CM_NCD'; 

TABLE_NAME                     PARTITION_NAME                 COMPRESS 
------------------------------ ------------------------------ -------- 
CM_NCD                         P03_CM_NCD                     DISABLED 
CM_NCD                         P01_CM_NCD                     DISABLED 
CM_NCD                         P02_CM_NCD                     DISABLED 
CM_NCD                         P02_CM_NCD                     DISABLED 
CM_NCD                         P02_CM_NCD                     DISABLED 
CM_NCD                         P02_CM_NCD                     DISABLED 
CM_NCD                         P02_CM_NCD                     DISABLED 
CM_NCD                         P01_CM_NCD                     DISABLED 
CM_NCD                         P02_CM_NCD                     DISABLED 
CM_NCD                         P01_CM_NCD                     DISABLED 
CM_NCD                         P01_CM_NCD                     DISABLED 
CM_NCD                         P01_CM_NCD                     DISABLED 
CM_NCD                         P01_CM_NCD                     DISABLED 
After verifying that all the partition and sub-partitions are uncompressed , i got the following error:
 
SQL> select count(*) from crm.cm_ncd; 
select count(*) from crm.cm_ncd 
* 
ERROR at line 1: 
ORA-12801: error signaled in parallel query server P005 
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type 
If all the partitions are uncompressed why i am getting this error message ?

Thanks
  • 1. Re: Uncompress subpartition table after restoring exadata to non-exadata storag
    robinsc Explorer
    Currently Being Moderated
    Check this article

    http://kerryosborne.oracle-guy.com/2011/01/ehcc-and-the-get_compression_type-function/

    As you probably already know, HCC is only applied to records loaded via direct path writes. Any updates cause rows to be migrated out of that storage format into blocks flagged for OLTP compression. Of course OLTP compression on a block only kicks in when a block is “full”. On top of this, altering a table to change it’s compression does not actually change the storage format of any existing records (unless you use the MOVE keyword). So you could load some data and then change the designation (say from QUERY LOW to QUERY HIGH). Rows that are inserted after the change will be stored in the new format (assuming the records are loaded via direct path writes of course). So why am I telling you all this. Well, because I ran across a statement in some Oracle documentation that said you can check to see what compression method a table is stored with by looking at the COMPRESS_FOR column in the DBA_TABLES view. This column does reveal what the table designation is. However, the setting actually only tells you how rows inserted in the future will be compressed. It tells you absolutely nothing about the way current rows are stored.


    SO though the table level field could be none there could still be compressed rows in the table...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points