Recently I have been asked to investigate the following error on an Exadata system.
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type
Well, that’s simple I thought! Must be (d)NFS mounted storage, right? Everyone knows that you can have HCC on Exadata (and a few other storage products). So I looked at the problem and soon found out that the data files in question all resided on the cells. Here is the sequence of events:
HCC is introduced to a range-list partitioned table, residing on TBS1 (all segments, no exception). The range partitions are in fact interval partitions
Subpartitions are moved to TBS2, this way they are compressed
TBS1 is no longer needed and dropped
Now when we tried to insert a row into the table we get the above error. As with so many Oracle errors, this one is not very helpful in the situation. The error only occurs when a new partition is automatically created… so here is the test case to demonstrate the problem.
You can see for yourself that everything is neatly stored on TBS1:
Personal thoughts
Two comments about the above configuration.
There are 400 entries in user_tab_subpartitions, but only 399 in user_segments. It turned out that P_MAN_SP5 wasn’t created due to deferred segment creation which has been left at its default value of TRUE. Interestingly all subpartitions were created for the automatically created interval partition although I had a bug in my data generation query: mod(rownum,4) will NEVER generate a value of 4.
This leads to point 2-now you are wasting space! Due to _partition_large_extents default value in my database (actually from 11.2.0.2 onwards) any partition will be 8 MB in size, even if there are only 250 (or 0 as in this case!) records in it…
Enter HCC Compression
Now I enable compression (note this is propagated to partitions and subpartitions):
This doesn’t compress anything, it merely enables compression if rows are inserted using direct path operations. Therefore I need to move the subpartitions which causes them to be compressed. In this scenario I have a dedicated tablespace for the compressed data, TBS2.
Since all segments for T1 are on TBS2, I can drop TBS1 which I did.
Now what has changed? The best answer you can get is from DBMS_METADATA:
As you can see only the subpartition definition changed and correctly reflect the move to TBS2. What’s going to hurt me is the reference to TBS1 in the STORE IN clause for the interval partition. And indeed:
Well I wonder if you could compress data on a non-existing tablespace ….
I tried various ways to change the store-in clause of the interval partition but for some reason didn’t find the correct syntax in the documentation Thanks to Andy Klock I finally managed to get around the issue, he found the correct syntax in a MOS note. And it does even exist in the documentation as alter_interval_partitioning clause.
Now I can force the new interval partitions to be created on TBS2:
The resulting metadata shows the change:
It now is possible to insert data again:
All is well again. You could now change the default attributes of the table and all partitions to TBS2 using commands similar to these:
The last command obviously has to be repeated for the partitions that have already been created. The DDL for the table now looks more healthy:
Problem solved! Although it shouldn’t really matter what’s in user_tab_partitions as these segments do not exist (only the subpartitions exist!)
Summary
The partition maintenance operation followed by dropping the default tablespace for new (interval) partitions caused Oracle to raise a misleading error message. Instead of the ORA-64307 error (stating the HCC is not supported on a void tablespace) a better error message would have been to let the user know that the tablespace did not exist.
This was on 11.2.0.3, I will try and test on 11.2.0.4 and 12.1.0.1 later and update the post.
