在给某一个大表加有default值的clob列时出现了Data file init write等待事件,这个等待事件是10gR2中新加入的,恰恰10gr2的文档(乃至11g的文档)都没有列出该等待事件。该等待事件一般在Oracle自动扩展数据文件(auto extend datafile)并串行地格式化数据文件的新创建的空间时作为前台(foreground)等待事件出现:
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 10.2.0.3 /* 数据库版本10.2.0.4,compatible为10.2.0.3 */ SQL> alter table tv add spare6 clob default '........'; SQL> col event for a30; SQL> select event, time_waited, current_file#, current_block# 2 from V$active_Session_History 3 where event like 'Data file init write' 4 order by sample_time desc; EVENT TIME_WAITED CURRENT_FILE# CURRENT_BLOCK# ------------------------------ ----------- ------------- -------------- Data file init write 533810 4 54783 Data file init write 442042 4 54783 Data file init write 47286 4 54783 Data file init write 42932 4 54783 Data file init write 413477 4 54783 Data file init write 153503 4 54783 Data file init write 33520 4 54783 Data file init write 307616 4 54783 Data file init write 214404 4 54783 Data file init write 3400 4 54783 Data file init write 212386 4 54783 Data file init write 192631 4 54783 .............. Data file init write 611157 4 54775 Data file init write 1379 4 54775 Data file init write 223541 4 54775
伴随着数据文件扩展(Data file init write等待事件的直接触发原因),Oracle需要通过以下递归dml语句维护相应的数据字典:
1.查询字典表file$确定表空间对应的数据文件号 select file# from file$ where ts#=:1 该操作可能伴随Data file init write等待事件发生: PARSING IN CURSOR #3 len=36 dep=2 uid=0 oct=3 lid=0 tim=1261083587010014 hv=1570213724 ad='8f7d4210' select file# from file$ where ts#=:1 END OF STMT ............. WAIT #14: nam='Data file init write' ela= 1091 count=1 intr=256 timeout=4294967295 obj#=57314 WAIT #14: nam='Data file init write' ela= 1078 count=1 intr=256 timeout=4294967295 obj#=57314 WAIT #14: nam='Data file init write' ela= 1102 count=1 intr=256 timeout=4294967295 obj#=57314 WAIT #14: nam='Data file init write' ela= 1156 count=1 intr=256 timeout=4294967295 obj#=57314 WAIT #14: nam='Data file init write' ela= 1870 count=1 intr=256 timeout=4294967295 obj#=57314 WAIT #14: nam='Data file init write' ela= 37 count=1 intr=256 timeout=4294967295 obj#=57314 WAIT #14: nam='Data file init write' ela= 4 count=4294967295 intr=32 timeout=2147483647 obj#=57314 2.若使用bigfile tablespace则可能出现以下insert seg$流程 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts, extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16, DECODE(:17,0,NULL,:17),:18) PARSE #15:c=0,e=368,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1261083586909081 BINDS #15: kkscoacd Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2ba79a0e1330 bln=22 avl=03 flg=05 value=1024 SQL> select file#,block#,blocks from sys.seg$ where file#=1024; FILE# BLOCK# BLOCKS ---------- ---------- ---------- 1024 82 13434880 1024 13434962 16384 1024 13451346 16384 1024 13467730 16384 1024 13484114 16384 /* 会出现奇怪的1024号文件 */ 3.维护用户表空间限额字典数据 update tsq$ set blocks = :3, maxblocks = :4, grantor# = :5, priv1 = :6, priv2 = :7, priv3 = :8 where ts# = :1 and user# = :2 4.更新seg$数据字典基表以扩展数据段 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, 6 5535, NULL, :14), cachehint = :15, hwmincr = :16, spare1 = DECODE(:17, 0, NULL, :17), scanhint = :18 where ts# = :1 and file# = :2 and block# = :3
以上数据文件空间扩展流程中只有查询语句”select file# from file$ where ts#=:1″伴随有”Data file init write”等待。