ddl操作是否会产生undo?

ddl是否会产生undo? 这可能是每一个初学Oracle的人都会有的疑问；ddl操作又不能rollback回滚，要什么undo数据呢？ 事实是几乎每个ddl操作都会产生undo,我们来探究一下:

<a href="http://blog.51cto.com/maclean/1277536#">?</a>

<code>SQL&gt; </code><code>select</code> <code>vs.</code><code>name</code><code>, ms.value</code>

<code>  </code><code>2    </code><code>from</code> <code>v$mystat ms, v$sysstat vs</code>

<code>  </code><code>3   </code><code>where</code> <code>ms.statistic# = vs.statistic#</code>

<code>  </code><code>4     </code><code>and</code> <code>name</code> <code>= </code><code>'undo change vector size'</code><code>;</code>

<code>NAME</code>                                                                  <code>VALUE</code>

<code>---------------------------------------------------------------- ----------</code>

<code>undo change vector </code><code>size</code>                                                   <code>0</code>

<code>SQL&gt; </code><code>create</code> <code>table</code> <code>YOUYUS (t1 </code><code>int</code><code>);</code>

<code>Table</code> <code>created.</code>

<code>SQL&gt;   </code><code>select</code> <code>vs.</code><code>name</code><code>, ms.value</code>

<code>  </code><code>2      </code><code>from</code> <code>v$mystat ms, v$sysstat vs</code>

<code>  </code><code>3     </code><code>where</code> <code>ms.statistic# = vs.statistic#</code>

<code>  </code><code>4       </code><code>and</code> <code>name</code> <code>= </code><code>'undo change vector size'</code><code>;</code>

<code>undo change vector </code><code>size</code>                                                <code>1992</code>

<code>/*</code><code>create</code> <code>table</code><code>的ddl语句产生了大约1992 bytes的撤销变化向量*/</code>

<code>SQL&gt; </code><code>drop</code> <code>table</code> <code>YOUYUS;</code>

<code>Table</code> <code>dropped.</code>

<code>undo change vector </code><code>size</code>                                                <code>4528</code>

<code>/* </code><code>drop</code> <code>table</code><code>语句产生2563 bytes的undo数据，多于</code><code>create</code> <code>table</code><code>；我们可以猜测</code><code>create</code> <code>table</code><code>时Oracle需要向基表中</code><code>insert</code><code>数据，而</code><code>drop</code> <code>table</code><code>时则需要</code><code>delete</code><code>/</code><code>update</code><code>数据，显然后者产生更多的undo*/</code>

<code>/*我们尝试创建一个由254个列组成的表*/</code>

<code>SQL&gt;     </code><code>select</code> <code>vs.</code><code>name</code><code>, ms.value</code>

<code>  </code><code>2        </code><code>from</code> <code>v$mystat ms, v$sysstat vs</code>

<code>  </code><code>3       </code><code>where</code> <code>ms.statistic# = vs.statistic#</code>

<code>  </code><code>4         </code><code>and</code> <code>name</code> <code>= </code><code>'undo change vector size'</code><code>;</code>

<code>create</code> <code>table</code> <code>YOUYUS (</code>

<code>t1 </code><code>int</code><code>,</code>

<code>t2 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t3 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t4 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t5 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t6 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t7 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t8 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t9 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>............................</code>

<code>t248 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t249 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t250 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t251 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t252 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t253 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code><code>,</code>

<code>t254 </code><code>char</code><code>(4) </code><code>default</code> <code>'oooo'</code>

<code>);</code>

<code>undo change vector </code><code>size</code>                                               <code>85832</code>

<code>/*产生了83k的undo，ddl所产生的undo量视乎其所要维护数据字典的操作类型和操作量*/</code>

<code>SQL&gt; oradebug setmypid;</code>

<code>Statement processed.</code>

<code>SQL&gt; oradebug event 10046 trace </code><code>name</code> <code>context forever,</code><code>level</code> <code>1;</code>

<code>undo change vector </code><code>size</code>                                              <code>214020</code>

<code>/*</code><code>drop</code> <code>产生了125k的undo*/</code>

<code>SQL&gt; oradebug tracefile_name;</code>

<code>/home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_5433.trc</code>

<code>/* 我们来看看</code><code>drop</code> <code>table</code> <code>到底做了哪些递归操作? */</code>

<code>[maclean@rh2 ~]$ cat PROD_ora_5433.trc|egrep </code><code>"delete|update"</code>

<code>         </code><code>'Need use delete_topo_geometry_layer() to deregister table '</code>

<code>select</code> <code>decode(u.type#, 2, u.ext_username, u.</code><code>name</code><code>), o.</code><code>name</code><code>,        t.</code><code>update</code><code>$, t.</code><code>insert</code><code>$, t.</code><code>delete</code><code>$, t.enabled,        decode(bitand(t.property, 8192),8192, 1, 0),        decode(bitand(t.property, 65536), 65536, 1, 0),       decode(bitand(t.property, 131072), 131072, 1, 0),       (</code><code>select</code> <code>o.</code><code>name</code> <code>from</code> <code>obj$ o          </code><code>where</code> <code>o.obj# = u.spare2 </code><code>and</code> <code>o.type# =57)  </code><code>from</code> <code>sys.obj$ o, sys.</code><code>user</code><code>$ u, sys.</code><code>trigger</code><code>$ t, sys.obj$ bo </code><code>where</code> <code>t.baseobject=bo.obj# </code><code>and</code> <code>bo.</code><code>name</code> <code>= :1 </code><code>and</code> <code>bo.spare3 = :2  </code><code>and</code> <code>bo.namespace = 1  </code><code>and</code> <code>t.obj#=o.obj# </code><code>and</code> <code>o.owner#=u.</code><code>user</code><code>#  </code><code>and</code> <code>o.type# = 12 </code><code>and</code> <code>bitand(property,16)=0 </code><code>and</code> <code>bitand(property,8)=0  </code><code>order</code> <code>by</code> <code>o.obj#</code>

<code>delete</code> <code>from</code> <code>object_usage </code><code>where</code> <code>obj# </code><code>in</code>  <code>(</code><code>select</code> <code>a.obj# </code><code>from</code> <code>object_usage a, ind$ b </code><code>where</code>  <code>a.obj# = b.obj# </code><code>and</code> <code>b.bo# = :1)</code>

<code>delete</code> <code>from</code> <code>sys.cache_stats_1$ </code><code>where</code> <code>dataobj# = :1</code>

<code>delete</code> <code>com$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>hist_head$ </code><code>where</code> <code>obj# = :1</code>

<code>delete</code> <code>from</code> <code>dependency$ </code><code>where</code> <code>d_obj#=:1</code>

<code>delete</code> <code>from</code> <code>source$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>compression$ </code><code>where</code> <code>obj#=:1</code>

<code>   </code><code>m_stmt:=</code><code>'delete from sdo_geor_ddl__table$$ where id=2'</code><code>;</code>

<code>   </code><code>m_stmt:=</code><code>'delete from sdo_geor_ddl__table$$'</code><code>;</code>

<code>delete</code> <code>from</code> <code>sdo_geor_ddl__table$$ </code><code>where</code> <code>id=2</code>

<code>delete</code> <code>from</code> <code>col$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>icol$ </code><code>where</code> <code>bo#=:1</code>

<code>delete</code> <code>from</code> <code>icoldep$ </code><code>where</code> <code>obj# </code><code>in</code> <code>(</code><code>select</code> <code>obj# </code><code>from</code> <code>ind$ </code><code>where</code> <code>bo#=:1)</code>

<code>delete</code> <code>from</code> <code>jijoin$ </code><code>where</code> <code>obj# </code><code>in</code> <code>( </code><code>select</code> <code>obj# </code><code>from</code> <code>jijoin$ </code><code>where</code> <code>tab1obj# = :1 </code><code>or</code> <code>tab2obj# = :1)</code>

<code>delete</code> <code>from</code> <code>jirefreshsql$ </code><code>where</code> <code>iobj# </code><code>in</code> <code>( </code><code>select</code> <code>iobj# </code><code>from</code> <code>jirefreshsql$ </code><code>where</code> <code>tobj# = :1)</code>

<code>delete</code> <code>from</code> <code>ccol$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>ind$ </code><code>where</code> <code>bo#=:1</code>

<code>delete</code> <code>from</code> <code>cdef$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>ecol$ </code><code>where</code> <code>tabobj# = :1</code>

<code>delete</code> <code>from</code> <code>tab$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>idl_ub1$ </code><code>where</code> <code>obj#=:1 </code><code>and</code> <code>part=:2</code>

<code>delete</code> <code>from</code> <code>idl_char$ </code><code>where</code> <code>obj#=:1 </code><code>and</code> <code>part=:2</code>

<code>delete</code> <code>from</code> <code>idl_ub2$ </code><code>where</code> <code>obj#=:1 </code><code>and</code> <code>part=:2</code>

<code>delete</code> <code>from</code> <code>idl_sb4$ </code><code>where</code> <code>obj#=:1 </code><code>and</code> <code>part=:2</code>

<code>delete</code> <code>from</code> <code>ncomp_dll$ </code><code>where</code> <code>obj#=:1 returning dllname </code><code>into</code> <code>:2</code>

<code>delete</code> <code>coltype$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>subcoltype$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>ntab$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>lob$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>refcon$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>opqtype$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>objauth$ </code><code>where</code> <code>obj#=:1</code>

<code>delete</code> <code>from</code> <code>obj$ </code><code>where</code> <code>obj# = :1</code>

<code>update</code> <code>seg$ </code><code>set</code> <code>type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,</code><code>user</code><code>#=:11,iniexts=:12,lists=decode(:13, 65535, </code><code>NULL</code><code>, :13),groups=decode(:14, 65535, </code><code>NULL</code><code>, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,</code><code>NULL</code><code>,:17),scanhint=:18, bitmapranges=:19 </code><code>where</code> <code>ts#=:1 </code><code>and</code> <code>file#=:2 </code><code>and</code> <code>block#=:3</code>

<code>delete</code> <code>from</code> <code>seg$ </code><code>where</code> <code>ts#=:1 </code><code>and</code> <code>file#=:2 </code><code>and</code> <code>block#=:3</code>

<code>/*如果ddl操作执行失败又会如何呢？*/</code>

<code>drop</code> <code>table</code> <code>YOUYUS</code>

<code>           </code><code>*</code>

<code>ERROR </code><code>at</code> <code>line 1:</code>

<code>ORA-00942: </code><code>table</code> <code>or</code> <code>view</code> <code>does </code><code>not</code> <code>exist</code>

<code>undo change vector </code><code>size</code>                                                 <code>264</code>

<code>/*同样产生了undo,量较少*/</code>

<code>/home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_5494.trc</code>

<code>[maclean@rh2 trace]$ cat PROD_ora_5494.trc|egrep </code><code>"update|insert|delete"</code>

<code>   </code><code>m_stmt:=</code><code>'insert into sdo_geor_ddl__table$$ values (1)'</code><code>;</code>

<code>   </code><code>m_stmt:=</code><code>'insert into sdo_geor_ddl__table$$ values (2)'</code><code>;</code>

<code>insert</code> <code>into</code> <code>sdo_geor_ddl__table$$ </code><code>values</code> <code>(2)</code>

<code>delete</code> <code>from</code> <code>sdo_geor_ddl__table$$</code>

<code>/*执行少量递归操作后，Oracle发现所要</code><code>drop</code><code>的对象并不存在，将会</code><code>rollback</code><code>之前的</code><code>"部分"</code><code>递归dml操作*/</code>

<code>其实我们可以把ddl操作分解为以下步骤:</code>

<code>begin</code>

<code>commit</code><code>;</code>

<code>--编译ddl</code>

<code>--实现ddl,包括一系列递归的数据字典维护操作及其他操作</code>

<code>exception</code>

<code>when</code> <code>others </code><code>then</code>

<code>rollback</code><code>;</code>

<code>end</code><code>;</code>

ddl操作无需也不允许手动commit或rollback参与，但这并不代表ddl操作不产生undo。

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277536