十二、全局临时表insert操作

1、创建测试环境
SQL>create global temporary table test_tmp
            (id number not null, object_name varchar2(20))
              on commit preserve rows;

SQL>col scn format 999999999990
SQL>select dbms_flashback.get_system_change_number scn from dual;

SQL>insert into test_tmp select rownum, object_name
          from dba_objects where rownum<100;
SQL>commit;

SQL>select dbms_flashback.get_system_change_number scn from dual;

SQL>alter system dump logfile
          '/home/xxxx/oracle/oradata/xxxxxx/redo03.log'
         scn min 56403090594
         scn max 56403090601;

2、trace文件解析

REDO RECORD - Thread:1 RBA: 0x005ffd.00047536.0010 LEN: 0x01e4 VLD: 0x05
SCN: 0x000d.21e2dca5 SUBSCN:  1 07/24/2009 15:34:39
CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x000d.21e2dc4e SEQ:  1 OP:5.2
ktudh redo: slt: 0x0002 sqn: 0x00005325 flg: 0x0012 siz: 308 fbi: 0
            uba: 0x00800d55.4af3.05    pxid:  0x0000.000.00000000
--更改undo段header          
 
CHANGE #2 TYP:0 CLS:30 AFN:2 DBA:0x00800d55 OBJ:4294967295 SCN:0x000d.21e2dc4c SEQ:  5 OP:5.1
ktudb redo: siz: 308 spc: 7682 flg: 0x0012 seq: 0x4af3 rec: 0x05
            xid:  0x0007.002.00005325
ktubl redo: slt: 2 rci: 0 opc: 11.1 objn: 143025 objd: 4210953 tsn: 3
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  Yes
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00800d55.4af3.01
prev ctl max cmt scn:  0x000d.21e2d06b  prev tx cmt scn:  0x000d.21e2d079
txn start scn:  0xffff.ffffffff  logon user: 55  prev brb: 8392012  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: QMD row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0040410a  hdba: 0x00404109
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 lock: 0 nrow: 99
slot[0]: 0
slot[1]: 1
.....
slot[98] 98

--只记录undo数据块的变化，没有记录数据块的变化
--因为临时表是批量insert，所以在undo中记录是批量delete(QMD)

REDO RECORD - Thread:1 RBA: 0x005ffd.00047537.0010 LEN: 0x008c VLD: 0x05
SCN: 0x000d.21e2dca8 SUBSCN:  1 07/24/2009 15:34:43
CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x000d.21e2dca5 SEQ:  1 OP:5.4
ktucm redo: slt: 0x0002 sqn: 0x00005325 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800d55.4af3.05 ext: 2 spc: 7372 fbi: 0

--commit

总结：

    全局临时表在redo中只记录undo数据块的变化，不会记录段的变化。