DDL与UNDO之间的关系

今天在网上找了些资料，整理了下DDL与UNDO之间的关系，通过后台发现DDL还是会产生一定的UNDO，实例演示如下：

SQL> select vs.name, ms.value
      from v$mystat ms, v$sysstat vs
      where ms.statistic# = vs.statistic#
      and name = 'undo change vector size';
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# = vs.statistic#
  4       and name = 'undo change vector size';
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# = vs.statistic#
  4       and name = 'undo change vector size';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                                4528

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

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

SQL>     select vs.name, ms.value
  2        from v$mystat ms, v$sysstat vs
  3       where ms.statistic# = vs.statistic#
  4         and name = 'undo change vector size';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                                   0

create table YOUYUS (
t1 int,
t2 char(4) default 'oooo',
t3 char(4) default 'oooo',
t4 char(4) default 'oooo',
t5 char(4) default 'oooo',
t6 char(4) default 'oooo',
t7 char(4) default 'oooo',
t8 char(4) default 'oooo',
t9 char(4) default 'oooo',
............................
t248 char(4) default 'oooo',
t249 char(4) default 'oooo',
t250 char(4) default 'oooo',
t251 char(4) default 'oooo',
t252 char(4) default 'oooo',
t253 char(4) default 'oooo',
t254 char(4) default 'oooo'
);

SQL>     select vs.name, ms.value
  2        from v$mystat ms, v$sysstat vs
  3       where ms.statistic# = vs.statistic#
  4         and name = 'undo change vector size';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                               85832

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

SQL> oradebug setmypid;
Statement processed.

如果确认要追踪其他哪个进程？可以查询spid或是oracle自己的pid
SQL> select a.username,a.sid ,a.serial#,b.spid
     from v$session a,v$process b
     where a.paddr=b.addr
/
------查询spid
以本次实验为例
USERNAME                            SID    SERIAL# SPID
------------------------------ ---------- ---------- ------------
SCOTT                                 155     1524    2204

SQL> select pid,spid,username from v$process;
       PID SPID         USERNAME
---------- ------------ ---------------
        23 2204         Administrator

设定追踪
SQL> oradebug setospid 2204
Oracle pid: 15, Windows thread id: 2204, image: ORACLE.EXE (SHAD)
或设定 SQL> oradebug setorapid 23

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# = vs.statistic#
  4         and name = 'undo change vector size';
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

SQL> oradebug event 10046 trace name context off;

statement processed.

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

[maclean@rh2 ~]$ cat PROD_ora_5433.trc|egrep "delete|update"
         'Need use delete_topo_geometry_layer() to deregister table '
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# = u.spare2 and o.type# =57)  from sys.obj$ o, sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject=bo.obj# and bo.name = :1 and bo.spare3 = :2  and bo.namespace = 1  and t.obj#=o.obj# and o.owner#=u.user#  and o.type# = 12 and bitand(property,16)=0 and bitand(property,8)=0  order by o.obj#
delete from object_usage where obj# in  (select a.obj# from object_usage a, ind$ b where  a.obj# = b.obj# and b.bo# = :1)
delete from sys.cache_stats_1$ where dataobj# = :1
delete com$ where obj#=:1
delete from hist_head$ where obj# = :1
delete from dependency$ where d_obj#=:1
delete from source$ where obj#=:1
delete from compression$ where obj#=:1
   m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
   m_stmt:='delete from sdo_geor_ddl__table$$';
delete from sdo_geor_ddl__table$$ where id=2
delete from col$ where obj#=:1
delete from icol$ where bo#=:1
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
delete from jijoin$ where obj# in ( select obj# from jijoin$ where tab1obj# = :1 or tab2obj# = :1)
delete from jirefreshsql$ where iobj# in ( select iobj# from jirefreshsql$ where tobj# = :1)
delete from ccol$ where obj#=:1
delete from ind$ where bo#=:1
delete from cdef$ where obj#=:1
delete ecol$ where tabobj# = :1
delete from tab$ where obj#=:1
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from col$ where obj#=:1
delete coltype$ where obj#=:1
delete from subcoltype$ where obj#=:1
delete ntab$ where obj#=:1
delete lob$ where obj#=:1
delete refcon$ where obj#=:1
delete from opqtype$ where obj#=:1
delete from cdef$ where obj#=:1
delete from objauth$ where obj#=:1
delete from obj$ where obj# = :1
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
delete from seg$ where ts#=:1 and file#=:2 and block#=:3

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

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# = vs.statistic#
  4         and name = 'undo change vector size';
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# = vs.statistic#
  4         and name = 'undo change vector size';
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@rh2 trace]$ cat PROD_ora_5494.trc|egrep "update|insert|delete"
         'Need use delete_topo_geometry_layer() to deregister table '
   m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
   m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
insert into sdo_geor_ddl__table$$ values (2)
   m_stmt:='delete from sdo_geor_ddl__table$$';
delete from sdo_geor_ddl__table$$

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

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

begin
commit;
--编译ddl
begin
--实现ddl,包括一系列递归的数据字典维护操作及其他操作
commit;
exception
when others then
rollback;
end;
end;