{"id":2312,"date":"2022-10-11T22:49:24","date_gmt":"2022-10-11T20:49:24","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=2312"},"modified":"2022-10-12T08:56:53","modified_gmt":"2022-10-12T06:56:53","slug":"ora-600-en-la-ejecucion-de-dbms_stats-gather_fixed_objects_stats","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/ora-600-en-la-ejecucion-de-dbms_stats-gather_fixed_objects_stats\/","title":{"rendered":"ORA-600 en la ejecucion de DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;"},"content":{"rendered":"<p>Hoy vamos a ver una sencilla entrada sobre un paso basico en un upgrade de base de datos.<\/p>\n<p>Antes de llevar a cabo un proceso de migracion debemos de asegurarnos que todas las estadisticas de la base de datos estan recientemente recopiladas ( menos de fos dias), <\/p>\n<p>Puede darse el caso de que ejecutando el paquete de obtencion de las estadisticas de los<em> Fixed Objects<\/em> recibamos el error <\/p>\n<pre>\r\n sqlplus \"\/as sysdba\"\r\nexecute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;\r\nBEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END;\r\n*\r\nERROR at line 1:\r\nORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:\r\n[1350], [3], [23], [60], [AMERICAN], [0], [], [], [], [], [], []\r\nORA-06512: at \"SYS.DBMS_STATS\", line 37237\r\nORA-06512: at line 1\r\n<\/pre>\n<p>Esto puede darse cuando tenemos la obtencion de estadisticas en auto_sample.<br \/>\nPara solucionarlo, nada mas facil que mover el porcentaje de estimacion a NULL <\/p>\n<p><b><br \/>\n exec dbms_stats.set_global_prefs(pname=>&#8217;ESTIMATE_PERCENT&#8217;,pvalue=>&#8217;NULL&#8217;);<br \/>\n<\/b><\/p>\n<pre>\r\n\r\nSQL>  select dbms_stats.get_prefs('ESTIMATE_PERCENT') from dual;\r\nDBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')\r\n--------------------------------------------------------------------------------\r\nDBMS_STATS.AUTO_SAMPLE_SIZE\r\n\r\nSQL> exec dbms_stats.set_global_prefs(pname=>'ESTIMATE_PERCENT',pvalue=>'NULL');\r\nPL\/SQL procedure successfully completed.\r\nSQL> select dbms_stats.get_prefs('estimate_percent') from dual;\r\nDBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')\r\n--------------------------------------------------------------------------------\r\nNULL\r\n<\/pre>\n<p>Con este sencillo paso podremos ejecutar nuestro paquete de obtencion de estadisticas en los objetos fijos sin eosos errores ORA<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hoy vamos a ver una sencilla entrada sobre un paso basico en un upgrade de base de datos. Antes de llevar a cabo un proceso de migracion debemos de asegurarnos que todas las estadisticas de la base de datos estan &hellip; <a href=\"http:\/\/clemente.pamplona.name\/dba\/ora-600-en-la-ejecucion-de-dbms_stats-gather_fixed_objects_stats\/\">Sigue leyendo <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[567,921,975,66,172,569],"tags":[1025,1022,1021,1024,1023,1003],"class_list":["post-2312","post","type-post","status-publish","format-standard","hentry","category-12c-en","category-18c","category-19c","category-errores-ora","category-migracion","category-ora-errors","tag-approximate-ndv-failed","tag-dbms_stats","tag-gather_fixed_objects_stats","tag-ora-20011","tag-ora-600","tag-upgrade"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2312","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=2312"}],"version-history":[{"count":2,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2312\/revisions"}],"predecessor-version":[{"id":2314,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/2312\/revisions\/2314"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=2312"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=2312"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=2312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}