修改oracle数据库字段类型，处理ORA-01439错误
<DIV><BR>对已经有数据的表修改字段类型时，Oracle提示：ORA-01439: 要更改数据类型, 则要修改的列必须为空。</DIV><DIV>可以创建新表，灌入原表数据后再改名，或者创建临时字段，替换数据后再删除。</DIV>
<DIV> </DIV>
<DIV>测试环境：<BR>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">drop</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> foo <SPAN style="COLOR: #0000cc">(</SPAN>col_name <SPAN style="COLOR: #0000ff">varchar2</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>5<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">insert</SPAN> <SPAN style="COLOR: #0000ff">into</SPAN> foo <SPAN style="COLOR: #0000ff">values</SPAN><SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'1'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">insert</SPAN> <SPAN style="COLOR: #0000ff">into</SPAN> foo <SPAN style="COLOR: #0000ff">values</SPAN><SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'12'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">insert</SPAN> <SPAN style="COLOR: #0000ff">into</SPAN> foo <SPAN style="COLOR: #0000ff">values</SPAN><SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'33445'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI>commit<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">index</SPAN> idx1 <SPAN style="COLOR: #0000ff">on</SPAN> foo <SPAN style="COLOR: #0000cc">(</SPAN>col_name<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #0000cc">*</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">desc</SPAN> foo</SPAN></LI></OL></DIV></DIV>
<DIV>解决方法</DIV>
<DIV> </DIV>
<DIV>1. 创建新表<BR>根据当前表结构创建新表，修改新表字段，将原数据灌进来，删除旧表，将新表rename为旧表名<BR>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> new <SPAN style="COLOR: #0000ff">as</SPAN> <SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #0000cc">*</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> foo <SPAN style="COLOR: #0000ff">where</SPAN> 1<SPAN style="COLOR: #0000cc">=</SPAN>2<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> new <SPAN style="COLOR: #0000ff">modify</SPAN> <SPAN style="COLOR: #0000cc">(</SPAN>col_name <SPAN style="COLOR: #0000ff">number</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>5<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">insert</SPAN> <SPAN style="COLOR: #0000ff">into</SPAN> new <SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #0000cc">*</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">drop</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">rename</SPAN> new <SPAN style="COLOR: #0000ff">to</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV>如果原表有索引，触发器、外键等需要新建。</DIV>
<DIV> </DIV>
<DIV>2. 使用CTAS来转换<BR>oracle 的使用查询创建表的功能创建一个新表包含原来的数据，命令如下： <BR>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> new<BR></LI>
<LI><SPAN style="COLOR: #0000ff">as</SPAN> <BR></LI>
<LI><SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #0000cc">[</SPAN>其他的列<SPAN style="COLOR: #0000cc">]</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <BR></LI>
<LI> <SPAN style="COLOR: #ff0000">to_number</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>Char_Col<SPAN style="COLOR: #0000cc">)</SPAN> col_name<BR></LI>
<LI><SPAN style="COLOR: #0000ff">from</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV>然后drop 原表，新表更名为原表： <BR>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">drop</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN> <BR></LI>
<LI><SPAN style="COLOR: #0000ff">rename</SPAN> new <SPAN style="COLOR: #0000ff">to</SPAN> foo<SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV>与方法1一样要注意新建相关对象。</DIV>
<DIV> </DIV>
<DIV>3. 创建临时字段替换<BR>新建一个临时字段，把要修改的字段的内容备份到临时字段后清空原字段，然后再修改类型，之后再把临时字段的内容复制到修改后的字段，最后删除临时字段</DIV>
<DIV>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> foo <SPAN style="COLOR: #0000ff">add</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>tmp_col <SPAN style="COLOR: #0000ff">number</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>5<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">update</SPAN> foo <SPAN style="COLOR: #0000ff">set</SPAN> tmp_col <SPAN style="COLOR: #0000cc">=</SPAN> <SPAN style="COLOR: #ff0000">to_number</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>col_name<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">update</SPAN> foo <SPAN style="COLOR: #0000ff">set</SPAN> col_name <SPAN style="COLOR: #0000cc">=</SPAN> <SPAN style="COLOR: #0000ff">null</SPAN><SPAN style="COLOR: #0000cc">;</SPAN> <SPAN style="COLOR: #ff9900">--此处要小心啊，原数据全没了，一定要保证上一步正确执行<BR></LI>
<LI></SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> foo <SPAN style="COLOR: #0000ff">modify</SPAN> <SPAN style="COLOR: #0000cc">(</SPAN>col_name <SPAN style="COLOR: #0000ff">number</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>5<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">update</SPAN> foo <SPAN style="COLOR: #0000ff">set</SPAN> col_name <SPAN style="COLOR: #0000cc">=</SPAN> tmp_col<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> foo <SPAN style="COLOR: #0000ff">drop</SPAN> <SPAN style="COLOR: #0000ff">column</SPAN> tmp_col<SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
页:
[1]