HCC error on Exadata after partitioning maintenance

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.

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):

MARTIN:mbach1> alter table T1 compress for query high;
Table altered.

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.

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.

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.