预计阅读时间：5分钟
某个系统上线，SQL脚本中有个变更，删除某张表的一个字段，线上执行，等了1个小时，还没有完成，担心是因为正常业务的影响，于是，尝试停了服务，可这条SQL，仍处于等待状态，此时，上游系统出现积Q，面临两个选择：
1. 继续等待SQL完成，但无法知晓，还要等待多久。
2. kill这条SQL，回滚DDL，同样需要些时间。
经过权衡，选择了方案2，大约2分钟左右，回滚完成，启动服务，恢复正常。虽然系统恢复，但是这个字段，没有被删除。
其实这张待删除字段的表，有5000万条记录，而且不是分区表，我们需要看下，是因为存在其他的事务，阻碍了删除操作，还是删除字段的操作中，有什么等待事件，导致如此之慢。
由于当时的环境，已经无法使用，所以只能用实验，模拟这个操作，首先，创建测试表，包含三个字段，插入测试数据，总计5000万，
SQL> create table test(id number, a varchar2(5), b varchar2(5));
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
50000000第一次执行，删除的过程中，提示了ORA-30036错误，这说明了什么？说明了alter table ... drop column ...操作，会产生回滚数据，需要占据回滚表空间，
SQL> alter table test drop column a;
alter table test drop column a
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'增加回滚空间，执行删除操作，此时这个会话hang，我们看下锁信息，发现当前drop column的会话，对TEST表，持有的锁模式，是6，即exclusive独占锁，
SQL> SELECT l.session_id sid,
2 s.serial#,
3 l.locked_mode,
4 l.oracle_username,
5 l.os_user_name,
6 s.machine,
7 s.terminal,
8 o.object_name,
9 s.logon_time
10 FROM v$locked_object l, all_objects o, v$session s
11 WHERE l.object_id = o.object_id
12 AND l.session_id = s.sid
13 ORDER BY sid, s.serial#;
SID SERIAL# LOCKED_MODE ORACLE_USERNAME OS_USER_NAME MACHINE TERMINAL OBJECT_NAME LOGON_TIM
------- ---------- ----------- --------------- --------------- ---------- ---------- ------------------------------ ---------
199 52583 3 BISAL oracle DBBAK1 pts/0 WRI$_OPTSTAT_HISTGRM_HISTORY 21-DEC-18
199 52583 6 BISAL oracle DBBAK1 pts/0 TEST 21-DEC-18
199 52583 3 BISAL oracle DBBAK1 pts/0 WRI$_OPTSTAT_HISTHEAD_HISTORY 21-DEC-18我们对这个操作，执行10046，得到的trace文件，有661K，打开trace，第一步操作，就是NOWAIT对表设置EXCLUSIVE锁，接下来，是一系列DML，
LOCK TABLE 'TEST' IN EXCLUSIVE MODE NOWAIT
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history
where :1 <= savtime="">and obj# = :2 and intcol# = nvl(:3, intcol#)
and rownum <=>4, rownum)
delete from sys.wri$_optstat_histhead_history h where h.obj#
in (select o2.obj# from sys.obj$ o1, sys.obj$ o2 where o1.obj# = :1
and o1.type# = 2 and o1.owner# = o2.owner#
delete from sys.wri$_optstat_histgrm_history h where h.obj#
in (select o2.obj# from sys.obj$ o1, sys.obj$ o2 where o1.obj# = :1
and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name)
and h.intcol# = :2
update sys.wri$_optstat_histhead_history h
set intcol# = decode(intcol#, :2, 0, intcol# -1)
where h.intcol# >= :2 and h.obj#
in (select o2.obj# from sys.obj$ o1, sys.obj$ o2 where o1.obj# = :1
and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name)
update sys.wri$_optstat_histgrm_history h
set intcol# = decode(intcol#, :2, 0, intcol# -1)
where h.intcol# >= :2 and h.obj#
in (select o2.obj# from sys.obj$ o1, sys.obj$ o2
where o1.obj# = :1 and o1.type# = 2 and o1.owner# = o2.owner#
and o2.name = o1.name)此时出现了很多等待事件，从记录的时间看，占据了drop column这个操作大部分的用时，
db file scattered read
db file sequential read
log buffer space
log file switch completion然后，就是对很多数据字典表，进行各种DML操作，此处忽略了select操作，
delete from sys.col_usage$ where obj#= :1 and intcol#= :2
delete from objauth$ where obj#=:1 and col#=:2
delete from col$ where obj#=:1 and intcol#=:2
update triggercol$ set col#=col#-:1
where (obj#=:2 or obj#=:3) and col#>:4
update col$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update icol$ set col#=col#-:1 where (bo#=:2 or bo#=:3) and col#>:4
update ccol$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update partcol$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update partcol$ set col#=col#-:1 where obj#
in (select obj# from ind$ where (bo#=:2 or bo#=:3)) and col#>:4
update histgrm$ set col#=col#-:1 where obj#
in (select o2.obj# from obj$ o1, obj$ o2
where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2
and o1.owner# = o2.owner# and o2.name = o1.name) and col#>:4
update hist_head$ set col#=col#-:1 where obj#
in (select o2.obj# from obj$ o1, obj$ o2
where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2
and o1.owner# = o2.owner# and o2.name = o1.name) and col#>:4
update coltype$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update lob$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update ntab$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update refcon$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update objauth$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update subpartcol$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update ind$ set trunccnt=trunccnt-:1 where (bo#=:2 or bo#=:3)
and trunccnt>:4
update partobj$ set definclcol=definclcol-:1
where (obj#=:2 or obj#=:3) and definclcol>:4
update indpart$ set inclcol=inclcol-:1 where (bo#=:2 or bo#=:3)
and inclcol>:4
update col$ set segcol#=segcol#-:1 where (obj#=:2 or obj#=:3)
and segcol#>:4
update icol$ set segcol#=segcol#-:1
where (bo#=:2 or bo#=:3) and segcol#>:4
update partcol$ set segcol#=segcol#-:1
where (obj#=:2 or obj#=:3) and segcol#>:4
update partcol$ set segcol#=segcol#-:1 where obj#
in (select obj# from ind$ where (bo#=:2 or bo#=:3)) and segcol#>:4
update subpartcol$ set segcol#=segcol#-:1
where (obj#=:2 or obj#=:3) and segcol#>:4
update triggercol$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update col$ set intcol#=intcol#-:1 where (obj#=:2 or obj#=:3) and intcol#>:4
update icol$ set spare2=spare2-:1 where (bo#=:2 or bo#=:3) and spare2>:4
update (select intcol#, ind$.bo# bo# from ind$, icol$ where ind$.obj# = icol$.obj#) set intcol#=intcol#-:1 where (bo#=:2 or bo#=:3) and intcol#>:4
update ccol$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update partcol$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update partcol$ set intcol#=intcol#-:1 where obj#
in (select obj# from ind$ where (bo#=:2 or bo#=:3)) and intcol#>:4
update histgrm$ set intcol#=intcol#-:1 where obj#
in (select o2.obj# from obj$ o1, obj$ o2
where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2
and o1.owner# = o2.owner# and o2.name = o1.name) and intcol#>:4
update hist_head$ set intcol#=intcol#-:1 where obj#
in (select o2.obj# from obj$ o1, obj$ o2
where (o1.obj#=:2 or o1.obj#=:3) and o1.type# = 2
and o1.owner# = o2.owner# and o2.name = o1.name) and intcol#>:4
update coltype$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update lob$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update ntab$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update refcon$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update rls_sc$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update fgacol$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update attrcol$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update com$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
update subpartcol$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update icoldep$ set intcol#=intcol#-:1
where obj# in (select obj# from ind$
where (bo#=:2 or bo#=:3)) and intcol#>:4
update association$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update ustats$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update partlob$ set intcol#=intcol#-:1
where (tabobj#=:2 or tabobj#=:3) and intcol#>:4
update trigger$ set nttrigcol=nttrigcol-:1
where (baseobject=:2 or baseobject=:3) and nttrigcol>:4
update viewtrcol$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update jijoin$ set tab1col#=tab1col#-:1
where (tab1obj#=:2 or tab1obj#=:3) and tab1col#>:4
update subcoltype$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update opqtype$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update opqtype$ set lobcol=lobcol-:1
where (obj#=:2 or obj#=:3) and lobcol>:4
update col_usage$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
update sumpred$ set lcolid=lcolid-:1
where (ldobj#=:2 or ldobj#=:3) and lcolid>:4
update coltype$ set typidcol#=typidcol#-:1
where (obj#=:2 or obj#=:3) and typidcol#>:4
update defsubpartlob$ set intcol#=intcol#-:1
where (bo#=:2 or bo#=:3) and intcol#>:4
update ecol$ set colnum=colnum-:1
where (tabobj#=:2 or tabobj#=:3) and colnum>:4
update radm_mc$ set intcol#=intcol#-:1
where (obj#=:2 or obj#=:3) and intcol#>:4
delete from compression$ where obj#=:1
delete from idl_ub1$ where obj#=:1
delete from idl_char$ where obj#=:1
delete from idl_ub2$ where obj#=:1
delete from idl_sb4$ where obj#=:1
delete from error$ where obj#=:1
update dependency$ set d_attrs = :1 where d_obj# = :2 and p_obj# = :3
delete from superobj$ where subobj# = :1
delete from tab_stats$ where obj#=:1
update tab$
set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),
tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,
samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),
dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
spare6=:35 where obj#=:1
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1
and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#,
userid,userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)
values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),:4,:5,:6,:7,:8,:9,:10,
:11,:12,cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,
:16,:17,:18,:19,:20,:21,:22)可以看出，一个drop column，在Oracle后台，就要做如此多的工作。尽管表结构、累积的统计信息、测试和生产环境，确实有些不同，但是实验中drop column操作，用时大约7分钟，和实际上线中，1小时未执行完成，有些不相符，从感性上看，删除一张表字段，不仅仅要删除数据字典，而且要删除实际表上的数据，因此这和表的数据量，很是有关。
如果有机会，在线上再做一次，或许能得到更多的信息。针对这个问题，朋友们要是有什么猜测，欢迎提出来。
其实针对大表的结构变更，还是要非常小心，一方面测试环境很重要，但往往可能不具备这种环境，或是因为数据量，或是因为并发量，但另一方面，要是了解原理，可能不用测试，就知道这个操作的影响，例如新增字段，可以参考
联系客服
