在线重定义大数据量表(新增字段)
<DIV>目的：在原始表TB_HXL_USER上新增字段remark01,默认值为'A',该表的数据量大概有4个亿,试着用普通的做法直接在原表上新增字段,执行了很长时间，最后报undo空间不足而失败,而且在新增字段的过程中，其他用户还不能访问该表,出现的等待事件是library cache lock.下面试着通过在线重定义的方法新增字段，能够避免undo空间不足以及其他用户不能访问该表的情况.</DIV><DIV> </DIV>
<DIV>1.使用如下SQL获取原始表的DDL</DIV>
<DIV>--设置分隔符号以及去掉表DDL中的storage属性</DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">begin</SPAN><BR></LI>
<LI> Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Set_Transform_Param<SPAN style="COLOR: #0000cc">(</SPAN>Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Session_Transform<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #ff00ff">'SQLTERMINATOR'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">True</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Set_Transform_Param<SPAN style="COLOR: #0000cc">(</SPAN>Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Session_Transform<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #ff00ff">'STORAGE'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">False</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">end</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV>－－提取表,索引,约束,以及权限的语句<BR>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">Select</SPAN> Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Get_Ddl<SPAN style="COLOR: #0000cc">(</SPAN>Object_Type <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TABLE'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> Name <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN><BR></LI>
<LI> Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Get_Dependent_Ddl<SPAN style="COLOR: #0000cc">(</SPAN>Object_Type <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'INDEX'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> Base_Object_Name <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN><BR></LI>
<LI> Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Get_Dependent_Ddl<SPAN style="COLOR: #0000cc">(</SPAN>Object_Type <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'CONSTRAINT'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> Base_Object_Name <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN><BR></LI>
<LI> Dbms_Metadata<SPAN style="COLOR: #0000cc">.</SPAN>Get_Dependent_Ddl<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'OBJECT_GRANT'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <SPAN style="COLOR: #ff00ff">'HXL'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">From</SPAN> Dual</SPAN></LI></OL></DIV></DIV>
<DIV>2.将步骤1 SQL中的表名 TB_HXL_USER 替换为 TB_HXL_USER_MID 创建中间表</DIV>
<DIV> </DIV>
<DIV>3.中间表新增字段 remark01</DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> TB_HXL_USER_MID <SPAN style="COLOR: #0000ff">add</SPAN> remark01 <SPAN style="COLOR: #0000ff">varchar2</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>10<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV>4.检查能否进行重定义,过程执行成功即说明可以重定义<BR>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">Begin</SPAN><BR></LI>
<LI> Dbms_Redefinition<SPAN style="COLOR: #0000cc">.</SPAN>Can_Redef_Table<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #0000ff">USER</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">End</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV>5.开始重定义表</DIV>
<DIV>注意:如原始表有未提交的事物,该过程会一直在等待,等待事件为enq: TX - row lock contention</DIV>
<DIV>--不能执行start_redef_table的情况下,需要将如下权限赋予用户<BR></DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">grant</SPAN> <SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">any</SPAN> <SPAN style="COLOR: #0000ff">table to hxl</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">grant</SPAN> <SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">any</SPAN> <SPAN style="COLOR: #0000ff">table to hxl</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">grant</SPAN> <SPAN style="COLOR: #0000ff">drop</SPAN> <SPAN style="COLOR: #0000ff">any</SPAN> <SPAN style="COLOR: #0000ff">table to hxl</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">grant</SPAN> <SPAN style="COLOR: #0000ff">lock</SPAN> <SPAN style="COLOR: #0000ff">any</SPAN> <SPAN style="COLOR: #0000ff">table to hxl</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">grant</SPAN> <SPAN style="COLOR: #0000ff">select</SPAN> <SPAN style="COLOR: #0000ff">any</SPAN> <SPAN style="COLOR: #0000ff">table to hxl</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">grant</SPAN> <SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">any</SPAN> <SPAN style="COLOR: #0000ff">trigger to hxl</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">grant</SPAN> <SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">any</SPAN> <SPAN style="COLOR: #0000ff">index to hxl</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV>--运行start_redef_table过程<BR>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">BEGIN</SPAN><BR></LI>
<LI> dbms_redefinition<SPAN style="COLOR: #0000cc">.</SPAN>start_redef_table<SPAN style="COLOR: #0000cc">(</SPAN><BR></LI>
<LI> uname <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #0000ff">USER</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> orig_table <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> int_table <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER_MID'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> options_flag <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> DBMS_REDEFINITION<SPAN style="COLOR: #0000cc">.</SPAN>cons_use_pk<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #ff9900">-- 如果有主键则是 options_flag => DBMS_REDEFINITION.cons_use_pk，如果没有DBMS_REDEFINITION.cons_use_rowid<BR></LI>
<LI></SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">END</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV>6.开始同步中间表<BR></DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">BEGIN</SPAN><BR></LI>
<LI> dbms_redefinition<SPAN style="COLOR: #0000cc">.</SPAN>sync_interim_table<SPAN style="COLOR: #0000cc">(</SPAN><BR></LI>
<LI> uname <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #0000ff">USER</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> orig_table <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> int_table <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER_MID'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">END</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV><BR>7.完成同步<BR>注意:如原始表有未提交的事物,该过程会一直在等待<BR>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">BEGIN</SPAN><BR></LI>
<LI> dbms_redefinition<SPAN style="COLOR: #0000cc">.</SPAN>finish_redef_table<SPAN style="COLOR: #0000cc">(</SPAN><BR></LI>
<LI> uname <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #0000ff">USER</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> orig_table <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> int_table <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_HXL_USER_MID'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">END</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<DIV>8.删除中间表</DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">drop</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> tb_hxl_user_mid<SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV><BR></DIV>
<DIV>9.修改索引名称</DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">index</SPAN> idx_tb_hxl_user_mid_n1 <SPAN style="COLOR: #0000ff">rename</SPAN> <SPAN style="COLOR: #0000ff">to</SPAN> idx_tb_hxl_user_n1<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">index</SPAN> idx_tb_hxl_user_mid_u1 <SPAN style="COLOR: #0000ff">rename</SPAN> <SPAN style="COLOR: #0000ff">to</SPAN> idx_tb_hxl_user_u1<SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
页:
[1]