{"id":14679,"date":"2020-04-25T12:33:05","date_gmt":"2020-04-25T12:33:05","guid":{"rendered":"https:\/\/ittutorial.org\/?p=14679"},"modified":"2020-04-25T12:48:55","modified_gmt":"2020-04-25T12:48:55","slug":"ora-38029-object-statistics-are-locked","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/ora-38029-object-statistics-are-locked\/","title":{"rendered":"ORA-38029: object statistics are locked"},"content":{"rendered":"<p>Hi,<\/p>\n<p>Sometimes You can get &#8220;<span class=\"msg\">ORA-38029: object statistics are locked <\/span><span class=\"msg\">\u00a0<\/span>&#8221; error.<\/p>\n<p><!--more--><\/p>\n<p>&nbsp;<\/p>\n<p>Details of error are as follows.<\/p>\n<pre><span class=\"msg\">ORA-38029: object statistics are locked<\/span><\/pre>\n<div class=\"msgexplan\"><span class=\"msgexplankw\">Cause:<\/span>\u00a0An attept was made to modify optimizer statistics of the object.<\/div>\n<div class=\"msgaction\"><span class=\"msgactionkw\">Action:<\/span>\u00a0Unlock statistics with the DBMS_STATS.UNLOCK_TABLE_STATS procedure on base table(s). Retry the operation if it is okay to update statistics.<\/div>\n<div><\/div>\n<div>ORA-38029: object statistics are locked<br \/>\nORA-20005: object statistics are locked (stattype = ALL)<\/div>\n<pre>\r\nSQL<b>&gt;<\/b>\u00a0analyze\u00a0index mehmet.salih_idx compute statistics;\r\nanalyze\u00a0index mehmet.salih_idx compute statistics\r\n<b>*<\/b>\r\nERROR at line\u00a01:\r\nORA<b>-<\/b>38029: object statistics are locked<\/pre>\n<pre><\/pre>\n<pre><\/pre>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<pre><\/pre>\n<p>FirstlyUnlock table statistics as follows.<\/p>\n<pre>exec dbms_stats.unlock_table_stats('SCHEMA_NAME', 'TABLE_NAME');\r\n\r\n\r\n\r\nSQL&gt; exec dbms_stats.unlock_table_stats('MEHMET', 'SALIH');\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>You can now gather table stats and analyze table again.<\/p>\n<pre><span class=\"crayon-e\">SQL<b>&gt;<\/b> exec dbms_stats.gather_table_stats('MEHMET', 'SALIH');\r\n\r\nPL<b>\/<\/b>SQL procedure successfully completed.<\/span><\/pre>\n<div><\/div>\n<div><\/div>\n<div>You can list all locked tables as follows,<\/div>\n<div>\n<pre>select * from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM','DBSNMP');<\/pre>\n<\/div>\n<p>&nbsp;<\/p>\n<p>And you can use the following script to generate all locked tables&#8217; unlock scripts.<\/p>\n<div>\n<pre>select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM','DBSNMP');<\/pre>\n<\/div>\n<div><\/div>\n<div><\/div>\n<div><span style=\"color: #ff0000;\"><strong>You can read the following post, if you don&#8217;t know what is the Database stats and how to gather table stats.<\/strong><\/span><\/div>\n<div><\/div>\n<div><a href=\"https:\/\/ittutorial.org\/dbms_stats-gather-database-stats-dictionary-and-fixed-object-stats-oracle\/\">https:\/\/ittutorial.org\/dbms_stats-gather-database-stats-dictionary-and-fixed-object-stats-oracle\/\u00a0<\/a><\/div>\n<div><\/div>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Oracle Database for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/\">https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, Sometimes You can get &#8220;ORA-38029: object statistics are locked \u00a0&#8221; error.<\/p>\n","protected":false},"author":1,"featured_media":6095,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[2111],"tags":[9242,9240,9239,9245,9241,9244,9243],"class_list":["post-14679","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-ora-errors","tag-dbms_stats-lock_table_stats-12c","tag-gather-stats-lock-table","tag-how-to-check-if-table-stats-are-locked-in-oracle","tag-object-statistics-are-locked","tag-ora-20005-object-statistics-are-locked-stattype-all-index","tag-ora-38029","tag-ora-38029-object-statistics-are-locked"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/ora-error-e1494274386931.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/14679","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=14679"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/14679\/revisions"}],"predecessor-version":[{"id":14680,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/14679\/revisions\/14680"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/6095"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=14679"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=14679"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=14679"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}