Statistics on Partitioned Tables - Part 6e - C ...

I'd bet regular readers might have guessed I'd never get back to the stats series, particularly given my extremely limited output this year Well, here goes ...

The theme of this post is already covered in the paper and the presentation, so if you've read either of those, then you might want to skip this. While working on the paper I realised that I'd made yet-another-incorrect-assumption about a copy_table_stats bug. I was right about the bug, which was described in an earlier post in the section titled "ORA-03113 / 07445 while copying list partition statistics". To recap, when copying statistics on multiple list subpartitions, Oracle disconnects the session and core dumps whilst copying the stats for the final OTHERS subpartition which is a DEFAULT list subpartition. I incorrectly assumed that this was because the OTHERS subpartition was the last subpartition that I was copying stats for, which seemed to make sense. But, as I was working on the paper, I thought I would have a dig around on My Oracle Support to see if there had been any reports of this bug.

Sure enough, there had been ....

Bug 10268597: DBMS_STATS.COPY_TABLE_STATS DISCONNECTS & GENERATES ORA-7445 [QOSPMINMAXPARTCOL] 

Which is still a bug in 10.2.0.5 and 11.2.0.2. The real issue occurs when you copy the statistics for a DEFAULT list subpartition and I was able to confirm this by changing the order in which I copied the subpartition statistics.

Here is the example from the original post.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_GROT', dstpartname => 'P_20100211_GROT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_JUNE', dstpartname => 'P_20100211_JUNE');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_HALO', dstpartname => 'P_20100211_HALO');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS');

SQL> show parameter user_dump_dest
ERROR:
ORA-03114: not connected to ORACLE 

If I change the order in which the subpartition stats are copied, it becomes clearer that the ORA-07445 occurs whenever I try to copy the OTHERS subpartition stats

SQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20110201_OTHERS', dstpartname => 'P_20110212_OTHERS');

BEGIN dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20110201_OTHERS', dstpartname => 'P_20110212_OTHERS'); END;

* 
ERROR at line 1: 
ORA-03113: end-of-file on communication channel 

SQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20110201_MOSCOW', dstpartname => 'P_20110212_MOSCOW');

ERROR: 
ORA-03114: not connected to ORACLE 

SQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20110201_LONDON', dstpartname => 'P_20110212_LONDON');

ERROR: 
ORA-03114: not connected to ORACLE 

SQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', 
                     srcpartname => 'P_20110201_SYDNEY', dstpartname => 'P_20110212_SYDNEY');

ERROR: 
ORA-03114: not connected to ORACLE