深入解析truncate执行过程_到底做了哪些变更?

2020-03-23 14:22:31

一、概念介绍

TRUNCATE语句可以快速且高效的删除表中的所有行。TRUNCATE语句只生成少量redo信息，并立即提交，是一个DDL语句且不能回滚。(Oracle 官方文档定义) 。

当使用truncate语句误删除业务表时，使用备份集恢复，再没有可用备份集时建议及时将表空间read only避免数据块被覆盖，然后选择data unload或bbed工具进行恢复。

本文主要使用10046 event、Oracle dump、自研工具多角度分析，深入解析“到底truncate做了哪些变更操作”?。

二、 ASSM offset解释

Segment header offset解释:

图1 Segment header offset解释

L2 bmb offset解释:

图2 L2 bmb offset解释

L1 bmb offset解释:

图3 L1 bmb offset解释

三、环境准备

create tablespace hsql datafile '/oradata/epmsn/hsql01.dbf'size 100M autoextend off;

create table hsql.trunc_10(c_char1 char(10),c_char2 char(10))tablespace hsql;

begin

for i in 1 .. 10000 loop

insert into hsql.trunc_10 values(i,'orastar');

end loop;

commit;

end;

alter system flush shared_pool;

alter system flush buffer_cache;

select * from hsql.TRUNC_10;

set linesize 200 pagesize 200

col owner for a10

col segment_name for a10

select owner,segment_name,header_file,header_block,SEGMENT_TYPEfrom dba_segments where segment_name='TRUNC_10';

OWNER SEGMENT_NA HEADER_FILE HEADER_BLOCKSEGMENT_TYPE

---------- ---------- ----------- ------------------------------

HSQL TRUNC_10 5 130 TABLE

SQL> set line 200

select owner,object_name,OBJECT_ID,DATA_OBJECT_ID fromdba_objects where object_name='TRUNC_10';SQL>

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID

---------- ------------------------------------

HSQL TRUNC_10 88036 88036

***dump段头块信息

alter sessionset tracefile_identifier='orastar_hdrseg_before_trunc_10';

oradebugsetmypid

alter systemdump datafile 5 block 130;

oradebugclose_trace

oradebugtracefile_name

输出结果:/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_105404_orastar_hdrseg_before_trunc_10.trc

******truncate操作

oradebug setmypid

oradebug event 10046 trace name context forever,level 12

truncate table hsql.trunc_10;

oradebug event 10046 trace name context off

oradebug tracefile_name

四、工具对比

Segment header:

图4 Segment header变更对比

说明：

蓝底：变更字段，经测试bbed可以不恢复

红底：变更字段，经测试bbed必须恢复

segment header变更信息：extents、blocks、ext、blk_ech、extsize_ech、hw、lhwmext、lhwmblk、lhwmextsize、lhwm、lhwmbmb1、hhwmbmb1、lastBMB1、mhext_echo、mhobj_ech、Extent Map(只保留个)、AuxillaryMap(只保留个)

L2 BMB变更对比:

图5 L2 BMB变更对比

L2 变更信息为：number、ffree、inc、opcode、objd。

L1 BMB 变更对比：（请重视第1个L1，变更信息必须恢复）--！！！！只有一个标深红的信息

图6 L1 BMB变更对比

L1 变更信息为：unformatted_bmb1、total_bmb1、nranges_bmb1、ffd_bmb1、objd_bmb1、deascnbas_bmb1

五、 10046 event

******分析10046日志变更情况

egrep -i -n '^update|^delete|^insert'/u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_105665.trc

[oracle@sourcedb trace]$ egrep -i -n'^update|^delete|^insert' /u01/app/oracle/diag/rdbms/epmsn/epmsn/trace/epmsn_ora_105665.trc

7135:update sys.mon_mods$ set inserts =inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags =(decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments =drop_segments + :dropseg, timestamp = :time where obj# = :objn

7343:delete from superobj$ where subobj# =:1

7620:delete from tab_stats$ where obj#=:1

7826:update tab$ setts#=: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=:35where obj#=:1

8276:update seg$ settype#=: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 wherets#=:1 and file#=:2 and block#=:3

8388: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 nulland linkname is null and subname is null

[oracle@sourcedb trace]$

10046 event 变更信息：

update sys.mon_mods$ --监控table变更

delete from superobj$

delete from tab_stats$ --删除统计信息

update tab$ --变更dataobj#，必须恢复

update seg$ --变更hwmincr（==object_id）

update obj$ --变更dataobj#，必须恢复