`
jinghuainfo
  • 浏览: 1515285 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

昨天做了次大表切换工作---失败了

 
阅读更多
<p>&lt;!--  [if gte mso 9]&gt;&lt;xml&gt;
&lt;w:WordDocument&gt;
  &lt;w:View&gt;Normal&lt;/w:View&gt;
  &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;
  &lt;w:PunctuationKerning/&gt;
  &lt;w:DrawingGridVerticalSpacing&gt;7.8 磅&lt;/w:DrawingGridVerticalSpacing&gt;
  &lt;w:DisplayHorizontalDrawingGridEvery&gt;0&lt;/w:DisplayHorizontalDrawingGridEvery&gt;
  &lt;w:DisplayVerticalDrawingGridEvery&gt;2&lt;/w:DisplayVerticalDrawingGridEvery&gt;
  &lt;w:ValidateAgainstSchemas/&gt;
  &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;
  &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;
  &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;
  &lt;w:Compatibility&gt;
   &lt;w:SpaceForUL/&gt;
   &lt;w:BalanceSingleByteDoubleByteWidth/&gt;
   &lt;w:DoNotLeaveBackslashAlone/&gt;
   &lt;w:ULTrailSpace/&gt;
   &lt;w:DoNotExpandShiftReturn/&gt;
   &lt;w:AdjustLineHeightInTable/&gt;
   &lt;w:BreakWrappedTables/&gt;
   &lt;w:SnapToGridInCell/&gt;
   &lt;w:WrapTextWithPunct/&gt;
   &lt;w:UseAsianBreakRules/&gt;
   &lt;w:DontGrowAutofit/&gt;
   &lt;w:UseFELayout/&gt;
  &lt;/w:Compatibility&gt;
  &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt;
&lt;/w:WordDocument&gt;
&lt;/xml&gt;&lt;![endif]--&gt;&lt;!--  [if gte mso 9]&gt;&lt;xml&gt;
&lt;w:LatentStyles DefLockedState="false" LatentStyleCount="156"&gt;
&lt;/w:LatentStyles&gt;
&lt;/xml&gt;&lt;![endif]--&gt;&lt;!--  [if !mso]&gt;
&lt;
classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui&gt;
&lt;/object&gt;
&lt;mce:style&gt;&lt;!--
st1\:*{behavior:url(#ieooui) }
--&gt;
&lt;!-- [endif]----&gt;
&lt;!--
/* Font Definitions */
@font-face
{font-family:宋体;
panose-1:2 1 6 0 3 1 1 1 1 1;
mso-font-alt:SimSun;
mso-font-charset:134;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:3 135135232 16 0 262145 0;}
@font-face
{font-family:"\@宋体";
panose-1:2 1 6 0 3 1 1 1 1 1;
mso-font-charset:134;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:3 135135232 16 0 262145 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:"";
margin:0cm;
margin-bottom:.0001pt;
text-align:justify;
text-justify:inter-ideograph;
mso-pagination:none;
font-size:10.5pt;
mso-bidi-font-size:12.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:宋体;
mso-font-kerning:1.0pt;}
span.javascript
{mso-style-name:javascript;}
/* Page Definitions */
@page
{mso-page-border-surround-header:no;
mso-page-border-surround-footer:no;}
@page Section1
{size:595.3pt 841.9pt;
margin:1.0cm 1.0cm 1.0cm 1.0cm;
mso-header-margin:42.55pt;
mso-footer-margin:49.6pt;
mso-paper-source:0;
layout-grid:15.6pt;}
div.Section1
{page:Section1;}
--&gt;
&lt;!--  [if gte mso 10]&gt;
&lt;mce:style&gt;&lt;!--
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
--&gt;
&lt;!-- [endif]----&gt;
</p>
<p class="MsoNormal"><span style="">
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">1 </span>
<span style="">有两个表想改成按某个时间的分区存储,原来是按另个时间来分区存储的。<span lang="EN-US">A</span>
表有<span lang="EN-US">21G</span>
,<span lang="EN-US">11786</span>
万行 分区未压缩。<span lang="EN-US">B</span>
表有<span lang="EN-US">9.5G</span>
<span lang="EN-US"><span> </span>
16442</span>
万条 分区压缩
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">2 </span>
<span style="">另外建立了两个目的表 分区压缩,增加两个新字段。各有<span lang="EN-US">16</span>
个存储过程向两个表<span lang="EN-US">MERGE</span>
数据。运行了很长时间,大约<span lang="EN-US">2-3</span>
周吧。在昨天周一上午对照两个表的上周的成功销售数据条数和金额,结果是一致的。心情很愉快!就通知其他同事要更改表结构。
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">3 </span>
<span style="">两套表和存储过程 分别有两个<span lang="EN-US">JOB</span>
调度。原来的存储过程<span lang="EN-US">JOB</span>
是在<span lang="EN-US">0</span>
点运行的。目的<span lang="EN-US">JOB</span>
是在<span lang="EN-US">10</span>
点运行的。
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">4 </span>
<span style="">在下午的时候,目的<span lang="EN-US">JOB</span>
已经运行完了<span lang="EN-US">16</span>
个存储过程,当它还在运行另外<span lang="EN-US">5</span>
个额外的过程,所以还得等待。不过这时候我把原来<span lang="EN-US">16</span>
个存储过程从库中删除了,在删除的时候多想了下,就保存在本地磁盘上并名字尾部追加<span lang="EN-US">001</span>

</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">5</span>
<span style="">然后在目的表生产索引,<span lang="EN-US">AB</span>
表上的索引和关键字的代码合在一起。然后放在<span lang="EN-US">LINUX</span>
下用<span lang="EN-US">SH</span>
脚本自动跑。原本想是很顺利的事情,可跑了<span lang="EN-US">1</span>
个多小时候,<span lang="EN-US">A</span>
表的跑完了,<span lang="EN-US">B</span>
表的索引也建完了,可<span lang="EN-US">B</span>
表的关键字去报错,有重复数据!
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">6 </span>
<span style="">郁闷啊,纠结啊!随后查下是什么样的数据重复了,看组成关键字那两个字段的值
</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> </span>
<em><span style="" lang="EN-US">/*+parallel(t,12)*/</span>
</em>

</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">F_ID,F_LOTID,f_projid</span>
<span style="" lang="EN-US">count</span>
<span style="" lang="EN-US">(*)<span> </span>
</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> T_B_TARGET T</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">group</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">by</span>
<span style="" lang="EN-US"><span> </span>
F_ID,F_LOTID, f_projid</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">having</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">count</span>
<span style="" lang="EN-US">(*)&gt;</span>
<span style="" lang="EN-US">1</span>

</p>
<p class="MsoNormal"><span style="">发现是那<span lang="EN-US">16</span>
个过程中其中一个过程里的一部分,特殊处理部分。该部分是把有些行数据<span lang="EN-US">F_Id</span>
前置成负数,以便与其他数据不发生关键字冲突。找到该过程,感觉原来已经处理过啊!这种感觉不是那种忽然间似曾面熟,似曾来过,似曾以前经历过。而确实真实修改过的。
不过今天想想,会不会上个月做数据库迁移时候,两边的库同时修改,同时运行,而对这不重要的过程,只修改了那边的而未修改这边的。
</span>
</p>
<p class="MsoNormal"><span style="">其实上面的代码在原来<span lang="EN-US">B</span>
表上并没有关键字,而是在<span lang="EN-US">B</span>
表的继承表才有关键字。从业务上来说只需要<span lang="EN-US">F_ID,F_LOTID</span>
两个字段做关键字,而<span lang="EN-US">F_PROJID</span>
是<span lang="EN-US">A</span>
表的一个关键字中字段。
</span>
</p>
<p class="MsoNormal"><span style="">因此采用以下语句删除重复字段
</span>
</p>
<p class="MsoNormal"><span class="javascript"><span style="" lang="EN-US">DELETE FROM </span>
</span>
<span style="" lang="EN-US">T_B_TARGET</span>
<span class="javascript"><span style="" lang="EN-US"> E</span>
</span>
<span style="" lang="EN-US"><br><span class="javascript">WHERE E.ROWID &gt; (SELECT MIN(X.ROWID) </span>
<br><span class="javascript">FROM </span>
</span>
<span style="" lang="EN-US">T_B_TARGET</span>
<span class="javascript"><span style="" lang="EN-US"> X</span>
</span>
<span style="" lang="EN-US"><br><span class="javascript">WHERE X.</span>
</span>
<span style="" lang="EN-US"> F_ID</span>
<span class="javascript"><span style="" lang="EN-US"> = E.</span>
</span>
<span style="" lang="EN-US"> F_ID</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">And x. F_LOTID=e. F_LOTID</span>
<span class="javascript"><span style="" lang="EN-US">);</span>
</span>
</p>
<p class="MsoNormal"><span class="javascript"><span style="">跑了近一个小时后,这是在数据库所在机器上的<span lang="EN-US">SQLPLUS</span>
上跑的,与我的电脑很远,中间通过防火墙连接,防火墙监听进程如果有多少分钟没操作就给断了!不过我这边通过<span lang="EN-US">SECURECRT</span>
连接的,其中设置了空闲发生某某去服务器。
</span>
</span>
</p>
<p class="MsoNormal"><span class="javascript"><span style="">结果 所删除的行数大于上面查询出来的数量。这就让我郁闷呢!再查下两边总数据量,发现原来<span lang="EN-US">B</span>
表有<span lang="EN-US">1.7</span>
亿条,而<span lang="EN-US">B</span>
目的表有<span lang="EN-US">1.55</span>
亿。怎么会这样呢?记得原来库上做这个目的表时候,是从原来表全导过来的,时间也没差多久啊!<span lang="EN-US">DBA</span>
做数据库迁移时候,已经把它迁移过来了。
</span>
</span>
</p>
<p class="MsoNormal"><span class="javascript"><span style="">怎么会差<span lang="EN-US">2</span>
千万条呢? 而且只能多不能少数据的,毕竟有个过程,也就是上面提到那个特殊处理部分,其实就是把<span lang="EN-US">A</span>
表的某些记录复制到<span lang="EN-US">B</span>
表中,并且<span lang="EN-US">F_ID</span>
置成负值。
</span>
</span>
</p>
<p class="MsoNormal"><span class="javascript"><span style="" lang="EN-US">7 </span>
</span>
<span class="javascript"><span style="">到目前只好重新做过<span lang="EN-US">B</span>
目的表了。在<span lang="EN-US">sqlplus</span>
上<span lang="EN-US">TRUNCATE </span>
该表<span lang="EN-US">,</span>
然后删除了索引,再把表置<span lang="EN-US">NOLOGGING</span>
状态。记得<span lang="EN-US">DBA</span>
说过如果整个表导过去的话,会要很长的时间。如果按分区导的话会很快的。不是<span lang="EN-US">EXPIMP</span>
方式。是<span lang="EN-US">INSERT INTO SELECT </span>
方式。
</span>
</span>
</p>
<p class="MsoNormal"><span class="javascript"><span style="">先找到<span lang="EN-US">B</span>
表的分区名称
</span>
</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> PARTITION_NAME </span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US">
dba_tab_partitions</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">where</span>
<span style="" lang="EN-US"> table_owner=</span>
<span style="" lang="EN-US">''</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">and</span>
<span style="" lang="EN-US"> table_name=</span>
<span style="" lang="EN-US">''</span>

</p>
<p class="MsoNormal"><span style="">再用工具 前插入
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">insert</span>
<em><span style="" lang="EN-US">/*+append*/</span>
</em>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">into</span>
<span style="" lang="EN-US"> T_B_TARGET
</span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> * </span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> T_B </span>
<span style="" lang="EN-US">partition</span>
<span style="" lang="EN-US">(</span>

</p>
<p class="MsoNormal"><span style="">后插入 </span>
<span style="" lang="EN-US">);</span>
</p>
<p class="MsoNormal"><span style="">本来每条后面追加<span lang="EN-US"> commit;
</span>
不过以前有过教训 <span lang="EN-US">LINUX VI</span>
下它不能认<span lang="EN-US">COMMIT;</span>
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">set
oracle_sid=FUKBA</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">date;</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US"><span></span>
sqlplus fuk/fuk2012@fukba &lt;&lt;EOF</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">insert</span>
<em><span style="" lang="EN-US">/*+append*/</span>
</em>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">into</span>
<span style="" lang="EN-US"> T_B_TARGET
</span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> * </span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> T_B </span>
<span style="" lang="EN-US">partition</span>
<span style="" lang="EN-US">(</span>
<span style="" lang="EN-US">P_0701);COMMIT;</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">EOF</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">Date;</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">NOHUP
SH SQL.SH &amp; </span>
<span style="">后它 <span lang="EN-US">cat nohup </span>
报 并行模式下不能修改对象
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">8 </span>
<span style="">都晚上<span lang="EN-US">10</span>
点了 <span></span>
把原来的过程编译进数据库里,还好保存下来了。把表名称改回来,把过程编译下,有个表没改名,继续改,继续编译。总于恢复到原来的样子
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">9 </span>
<span style="">在<span lang="EN-US">SQLPLUS</span>
试了下<span lang="EN-US"> insert into </span>
一个大分区 需要<span lang="EN-US">3</span>
分钟 几千万条数据就进去了。然后在<span lang="EN-US">SH</span>
脚本里手工加入<span lang="EN-US">COMMIT</span>
; <span lang="EN-US">46</span>
行啊!
</span>
</p>
<p class="MsoNormal"><span style="">跑完后 花费了<span lang="EN-US">46</span>
分钟。
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">10 </span>
<span style="">接着在<span lang="EN-US">B</span>
目的表上先上关键字。没多久报错
还是有重复数据!查下数据量 共有<span lang="EN-US">2.2</span>
亿条! 我的天啊,不想让我回窝睡了吗?
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">11
10:30</span>
<span style="">了 再次清空表 一次行插入全部数据 </span>
<span style="" lang="EN-US">insert</span>
<em><span style="" lang="EN-US">/*+append*/</span>
</em>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">into</span>
<span style="" lang="EN-US"> T_B_TARGET
</span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> /*+parallel(b,12)*/* </span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> T_B</span>
<span style="" lang="EN-US"> b</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">12 </span>
<span style="">全插了<span lang="EN-US">1</span>
个小时结束了!建关键字 。都<span lang="EN-US">11:30</span>
了 再耗下去 就不能回郊区窝里了!怎么办? 先查下数据量,嗯<span lang="EN-US">1.7</span>
亿条。
</span>
</p>
<p class="MsoNormal"><span style="">应该不会有重复数据的,因为在原<span lang="EN-US">B</span>
表上核对过没有重复数据的。应该放心的。好吧!把关键字语句放到<span lang="EN-US">SQLPLUS</span>
上跑。另外两个索引放到<span lang="EN-US">SH</span>
脚本里跑。
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US"></span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US">13 </span>
<span style="">今天早上过来 看到关键字建立起来了,花费了<span lang="EN-US">51</span>
分。索引也建好了,从<span lang="EN-US">11:27</span>
分到<span lang="EN-US">12:59</span>
分。我考!还是英明的回窝睡了!
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US"></span>
</p>
<p class="MsoNormal"><span style="">最后总结下 总的来说《实践是检验真理的唯一标准》 不动手亲子做,原来认为,原来以为,原来应该,原来大脑觉得事。到了实践一切皆有可能变得糟糕!
<span lang="EN-US">VI</span>
下面的<span lang="EN-US">SH</span>
脚本里运行<span lang="EN-US">SQL</span>
语句,估计要分行确认<span lang="EN-US">COMMIT</span>
; 也就是说两个语句不能同在一行。<span lang="EN-US">SQLPLUS</span>
里还遇到了个问题,执行了两个语句后,再执行第三个语句,它报错了。从错误上看第二条和第三条语句连在一起,也就是第二条语句没执行过。
</span>
</p>
<p class="MsoNormal"><span style="">复制过去的第三条语句被追加到了第二条语句尾部。 不知道是什么原因!
当它给我错误的认识,让我感到第二条语句已经执行完了,让我错误地执行了其他工作!
</span>
</p>
<p class="MsoNormal"><span style="" lang="EN-US"><span></span>
</span>
<span style="">其实说白了 在<span lang="EN-US">LINUX</span>
下很多细节要注意的,这些细节影响了你的工作效率,并且制造了麻烦!
</span>
</p>
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics