{"id":113,"date":"2015-04-01T19:45:19","date_gmt":"2015-04-01T17:45:19","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=113"},"modified":"2015-11-17T20:27:22","modified_gmt":"2015-11-17T19:27:22","slug":"oracle-automatic-maintenance-expdp-and-ora-01466","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2015\/04\/01\/oracle-automatic-maintenance-expdp-and-ora-01466\/","title":{"rendered":"Oracle automatic maintenance, expdp and ORA-01466"},"content":{"rendered":"<p>At a client request I&#8217;ve implemented a daily consistent export of few schemas, using something like:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nexpdp &quot;\/ as sysdba&quot; SCHEMAS=user1 dumpfile=export_%1_%datetime%.dmp logfile=export_%1_%datetime%.log flashback_time=systimestamp\r\n<\/pre>\n<p>I was set to run at 22:00 every night.<\/p>\n<p>Next day the log showed some errors like:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nORA-31693: Table data object &quot;USER1&quot;.&quot;TABLE_TEST&quot; failed to load\/unload and is being skipped due to error:\r\nORA-02354: error in exporting\/importing data\r\nORA-01466: unable to read data - table definition has changed\r\n<\/pre>\n<p>Looking closer, it was due to the automatic statistics collection, done within the daily maintenance window of Oracle. The LAST_ANALYZED column of DBA_TABLES changed, as well as LAST_DDL_TIME on DBA_OBJECTS. I did not expect neither I see nowhere else complaining about this interference between the maintenance windows and a export data pump. Strange!<\/p>\n<p>To avoid any problem in the future we decided to change the maintenance window time and lenght.<br \/>\nOn Oracle 11g and 12c there is a window per day. So to change lets say to 19:00 and having a duration of 2 hours you do:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=19;byminute=0; bysecond=0');\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',attribute =&gt; 'DURATION',value     =&gt; numtodsinterval(2, 'hour'));\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=19;byminute=0; bysecond=0');\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',attribute =&gt; 'DURATION',value     =&gt; numtodsinterval(2, 'hour'));\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=19;byminute=0; bysecond=0');\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW',attribute =&gt; 'DURATION',value     =&gt; numtodsinterval(2, 'hour'));\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=19;byminute=0; bysecond=0');\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW',attribute =&gt; 'DURATION',value     =&gt; numtodsinterval(2, 'hour'));\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=19;byminute=0; bysecond=0');\r\nEXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',attribute =&gt; 'DURATION',value     =&gt; numtodsinterval(2, 'hour'));\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>At a client request I&#8217;ve implemented a daily consistent export of few schemas, using something like: expdp &quot;\/ as sysdba&quot; SCHEMAS=user1 dumpfile=export_%1_%datetime%.dmp logfile=export_%1_%datetime%.log flashback_time=systimestamp I was set to run at 22:00 every night. Next day the log showed some errors like: ORA-31693: Table data object &quot;USER1&quot;.&quot;TABLE_TEST&quot; failed to load\/unload and is being skipped due to [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,6,2,7],"tags":[],"class_list":{"0":"post-113","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-datapump","7":"category-oracle","8":"category-oracle-11g","9":"category-oracle-12c","10":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=113"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/113\/revisions"}],"predecessor-version":[{"id":114,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/113\/revisions\/114"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}