undochangevectorsizeoracle,ddl操作是否会产生undo?

ddl是否会产生undo?

这可能是每一个初学Oracle的人都会有的疑问&＃xff1b;ddl操作又不能rollback回滚&＃xff0c;要什么undo数据呢&＃xff1f;

事实是几乎每个ddl操作都会产生undo,我们来探究一下:

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 0

SQL> create table YOUYUS (t1 int);

Table created.

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 1992

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

SQL> drop table YOUYUS;

Table dropped.

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 4528

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

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

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 0

create table YOUYUS (

t1 int,

t2 char(4) default &＃39;oooo&＃39;,

t3 char(4) default &＃39;oooo&＃39;,

t4 char(4) default &＃39;oooo&＃39;,

t5 char(4) default &＃39;oooo&＃39;,

t6 char(4) default &＃39;oooo&＃39;,

t7 char(4) default &＃39;oooo&＃39;,

t8 char(4) default &＃39;oooo&＃39;,

t9 char(4) default &＃39;oooo&＃39;,

............................

t248 char(4) default &＃39;oooo&＃39;,

t249 char(4) default &＃39;oooo&＃39;,

t250 char(4) default &＃39;oooo&＃39;,

t251 char(4) default &＃39;oooo&＃39;,

t252 char(4) default &＃39;oooo&＃39;,

t253 char(4) default &＃39;oooo&＃39;,

t254 char(4) default &＃39;oooo&＃39;

);

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 85832

/*产生了83k的undo&＃xff0c;ddl所产生的undo量视乎其所要维护数据字典的操作类型和操作量*/

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 1;

Statement processed.

SQL> drop table YOUYUS;

Table dropped.

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 214020

/*drop 产生了125k的undo*/

SQL> oradebug tracefile_name;

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

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

[maclean&＃64;rh2 ~]$ cat PROD_ora_5433.trc|egrep "delete|update"

&＃39;Need use delete_topo_geometry_layer() to deregister table &＃39;

select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$, t.insert$, t.delete$, 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), (select o.name from obj$ o where o.obj# &＃61; u.spare2 and o.type# &＃61;57) from sys.obj$ o, sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject&＃61;bo.obj# and bo.name &＃61; :1 and bo.spare3 &＃61; :2 and bo.namespace &＃61; 1 and t.obj#&＃61;o.obj# and o.owner#&＃61;u.user# and o.type# &＃61; 12 and bitand(property,16)&＃61;0 and bitand(property,8)&＃61;0 order by o.obj#

delete from object_usage where obj# in (select a.obj# from object_usage a, ind$ b where a.obj# &＃61; b.obj# and b.bo# &＃61; :1)

delete from sys.cache_stats_1$ where dataobj# &＃61; :1

delete com$ where obj#&＃61;:1

delete from hist_head$ where obj# &＃61; :1

delete from dependency$ where d_obj#&＃61;:1

delete from source$ where obj#&＃61;:1

delete from compression$ where obj#&＃61;:1

m_stmt:&＃61;&＃39;delete from sdo_geor_ddl__table$$ where id&＃61;2&＃39;;

m_stmt:&＃61;&＃39;delete from sdo_geor_ddl__table$$&＃39;;

delete from sdo_geor_ddl__table$$ where id&＃61;2

delete from col$ where obj#&＃61;:1

delete from icol$ where bo#&＃61;:1

delete from icoldep$ where obj# in (select obj# from ind$ where bo#&＃61;:1)

delete from jijoin$ where obj# in ( select obj# from jijoin$ where tab1obj# &＃61; :1 or tab2obj# &＃61; :1)

delete from jirefreshsql$ where iobj# in ( select iobj# from jirefreshsql$ where tobj# &＃61; :1)

delete from ccol$ where obj#&＃61;:1

delete from ind$ where bo#&＃61;:1

delete from cdef$ where obj#&＃61;:1

delete ecol$ where tabobj# &＃61; :1

delete from tab$ where obj#&＃61;:1

delete from idl_ub1$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_char$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_ub2$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_sb4$ where obj#&＃61;:1 and part&＃61;:2

delete from ncomp_dll$ where obj#&＃61;:1 returning dllname into :2

delete from idl_ub1$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_char$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_ub2$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_sb4$ where obj#&＃61;:1 and part&＃61;:2

delete from ncomp_dll$ where obj#&＃61;:1 returning dllname into :2

delete from idl_ub1$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_char$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_ub2$ where obj#&＃61;:1 and part&＃61;:2

delete from idl_sb4$ where obj#&＃61;:1 and part&＃61;:2

delete from ncomp_dll$ where obj#&＃61;:1 returning dllname into :2

delete from col$ where obj#&＃61;:1

delete coltype$ where obj#&＃61;:1

delete from subcoltype$ where obj#&＃61;:1

delete ntab$ where obj#&＃61;:1

delete lob$ where obj#&＃61;:1

delete refcon$ where obj#&＃61;:1

delete from opqtype$ where obj#&＃61;:1

delete from cdef$ where obj#&＃61;:1

delete from objauth$ where obj#&＃61;:1

delete from obj$ where obj# &＃61; :1

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

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

delete from seg$ where ts#&＃61;:1 and file#&＃61;:2 and block#&＃61;:3

/*如果ddl操作执行失败又会如何呢&＃xff1f;*/

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 1;

Statement processed.

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 0

SQL> drop table YOUYUS;

drop table YOUYUS

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select vs.name, ms.value

2 from v$mystat ms, v$sysstat vs

3 where ms.statistic# &＃61; vs.statistic#

4 and name &＃61; &＃39;undo change vector size&＃39;;

NAME VALUE

---------------------------------------------------------------- ----------

undo change vector size 264

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

SQL> oradebug tracefile_name;

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

[maclean&＃64;rh2 trace]$ cat PROD_ora_5494.trc|egrep "update|insert|delete"

&＃39;Need use delete_topo_geometry_layer() to deregister table &＃39;

m_stmt:&＃61;&＃39;insert into sdo_geor_ddl__table$$ values (1)&＃39;;

m_stmt:&＃61;&＃39;insert into sdo_geor_ddl__table$$ values (2)&＃39;;

insert into sdo_geor_ddl__table$$ values (2)

m_stmt:&＃61;&＃39;delete from sdo_geor_ddl__table$$&＃39;;

delete from sdo_geor_ddl__table$$

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

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

begin

commit;

--编译ddl

begin

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

commit;

exception

when others then

rollback;

end;

end;

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