ddl是否会产生undo? 这可能是每一个初学Oracle的人都会有的疑问；ddl操作又不能rollback回滚，要什么undo数据呢？ 事实是几乎每个ddl操作都会产生undo,我们来探究一下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
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
扫一扫
2617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
