ddl操作是否会产生undo?-白红宇

ddl是否会产生undo? 这可能是每一个初学Oracle的人都会有的疑问；ddl操作又不能rollback回滚，要什么undo数据呢？事实是几乎每个ddl操作都会产生undo,我们来探究一下:
                         
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> 
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.
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
/* 我们来看看
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
;
        
      
ddl操作无需也不允许手动commit或rollback参与，但这并不代表ddl操作不产生undo。
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277536