an earlier post<\/a> 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.<\/p>\nSure enough, there had been …. <\/p>\n
Bug 10268597: DBMS_STATS.COPY_TABLE_STATS DISCONNECTS & GENERATES ORA-7445 [QOSPMINMAXPARTCOL]\u00a0 <\/p>\n
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. <\/p>\n
Here is the example from the original post.<\/p>\n
SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srcpartname => 'P_20100209_GROT', dstpartname => 'P_20100211_GROT');\n\nPL\/SQL procedure successfully completed.\n\nSQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srcpartname => 'P_20100209_JUNE', dstpartname => 'P_20100211_JUNE');\n\nPL\/SQL procedure successfully completed.\n\nSQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srcpartname => 'P_20100209_HALO', dstpartname => 'P_20100211_HALO');\n\nPL\/SQL procedure successfully completed.\n\nSQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS');\n\nSQL> show parameter user_dump_dest\nERROR:\nORA-03114: not connected to ORACLE \n<\/pre>\nIf 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<\/p>\n
SQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', \n srcpartname => 'P_20110201_OTHERS', dstpartname => 'P_20110212_OTHERS');\n\nBEGIN dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', \n srcpartname => 'P_20110201_OTHERS', dstpartname => 'P_20110212_OTHERS'); END;\n\n* \nERROR at line 1: \nORA-03113: end-of-file on communication channel \n\nSQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', \n srcpartname => 'P_20110201_MOSCOW', dstpartname => 'P_20110212_MOSCOW');\n\nERROR: \nORA-03114: not connected to ORACLE \n\nSQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', \n srcpartname => 'P_20110201_LONDON', dstpartname => 'P_20110212_LONDON');\n\nERROR: \nORA-03114: not connected to ORACLE \n\nSQL> exec dbms_stats.copy_table_stats(ownname => 'PERF_SUPPORT', tabname => 'TEST_TAB1', \n srcpartname => 'P_20110201_SYDNEY', dstpartname => 'P_20110212_SYDNEY');\n\nERROR: \nORA-03114: not connected to ORACLE \n<\/pre>\n","protected":false},"excerpt":{"rendered":"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.… Continue reading Statistics on Partitioned Tables – Part 6e – COPY_TABLE_STATS – Bug<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1637","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1602,"url":"http:\/\/orcldoug.com\/blog\/2010\/05\/07\/statistics-on-partitioned-tables-part-6d-copy_table_stats-a-light-bulb-moment\/","url_meta":{"origin":1637,"position":0},"title":"Statistics on Partitioned Tables – Part 6d – COPY_TABLE_STATS – A Light-bulb Moment","date":"May 7, 2010","format":false,"excerpt":"I'm pretty self-concious of the amount of waffle that surrounds any technical content here, so let's get the technical bit out of the way first, then the waffling can come later ...I finally tracked down the mistake I didn't make in part 6a, but thought I'd identified and fixed in\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1596,"url":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6a-copy_table_stats-intro\/","url_meta":{"origin":1637,"position":1},"title":"Statistics on Partitioned Tables – Part 6a – COPY_TABLE_STATS – Intro","date":"April 22, 2010","format":false,"excerpt":"[Phew. At last. The first draft of this was dated more than two weeks ago .... One of the problems with blogging about copying stats was the balance between explaining it and pointing out some of the problems I've encountered. So I've broken up this post, with a little explanation\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1597,"url":"http:\/\/orcldoug.com\/blog\/2014\/05\/02\/statistics-on-partitioned-tables-part-6c-copy_table_stats-bugs-and-patches\/","url_meta":{"origin":1637,"position":2},"title":"Statistics on Partitioned Tables – Part 6c – COPY_TABLE_STATS – Bugs and Patches","date":"May 2, 2014","format":false,"excerpt":"I wanted to talk about a few of the bugs and patches you need to be aware of if you plan to use DBMS_STATS.COPY_TABLE_STATS. Believe me, when entering the world of stats on (sub-)partitioned objects, you had better be prepared to spend a lot of time on My Oracle Support\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1598,"url":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6b-copy_table_stats-mistakes\/","url_meta":{"origin":1637,"position":3},"title":"Statistics on Partitioned Tables – Part 6b – COPY_TABLE_STATS – Mistakes","date":"April 22, 2010","format":false,"excerpt":"Sigh ... these posts have become a bit of a mess. There are so many different bits and pieces I want to illustrate and I've been trying to squeeze them in around normal work. Worse still, because I keep leaving them then coming back to them and re-running tests it's\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1590,"url":"http:\/\/orcldoug.com\/blog\/2010\/03\/28\/statistics-on-partitioned-tables-contents\/","url_meta":{"origin":1637,"position":4},"title":"Statistics on Partitioned Tables – Contents","date":"March 28, 2010","format":false,"excerpt":"When Jonathan Lewis decided it was time to post a list of the Partition Stats posts on his blog and Noons suggested I made them easier to track down, I listened. So this post will link to the others and, at least in the short term, I've also included links\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1570,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-5\/","url_meta":{"origin":1637,"position":5},"title":"Statistics on Partitioned Tables – Part 5","date":"February 28, 2010","format":false,"excerpt":"Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle. The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some\u2026","rel":"","context":"With 18 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1637","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1637"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1637\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1637"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1637"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1637"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}