不产生UNDO的情况

以下操作不产生UNDO或产生很少的undo 1 Read-Only transaction 2Direct Path 数据导入 3对临时段操作，如排序等。其他情况，因为要保证一致读，都要产生必要的undo 以下操作不产生UNDO 1 Read-Only transaction SQL> set transaction read only; Transaction set. SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=(select sid from v$mystat where rownum=1)); no rows selected SQL> insert into test select * from t; insert into test select * from t              * ERROR at line 1: ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=(select sid from v$mystat where rownum=1)); no rows selected 2Direct Path 数据导入 SQL> create table test as select * from t where 1=0; Table created. SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=(select sid from v$mystat where rownum=1)); no rows selected SQL> insert /*+ append */ into test select * from t; 58842 rows created. SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec ,used_ublk,used_ur ec from v$transaction where ses_addr=(select saddr from v$session where sid=(sel ect sid from v$mystat where rownum=1));     XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC ---------- ---------- ---------- ---------- ---------- ---------- ---------- USED_UBLK USED_UREC ---------- ----------          2         33      10273          0          0          0          0          1          1 SQL> commit; Commit complete. SQL> insert into test select * from t; 58842 rows created. SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=(select sid from v$mystat where rownum=1)); USED_UBLK USED_UREC ---------- ----------         27       1482
    Append插入数据未提交时候，dump回滚段头 Start dump data blocks tsn: 1 file#: 2 minblk 25 maxblk 25 buffer tsn: 1 rdba: 0x00800019 (2/25) scn: 0x0000.00984d1c seq: 0x01 flg: 0x04 tail: 0x4d1c2601 frmt: 0x02 chkval: 0xaf28 type: 0x26=KTU SMU HEADER BLOCK TRN TBL:: index state cflags wrap#    uel         scn            dba            parent-xid    nub     stmt_num ------------------------------------------------------------------------------------------------    0x21   10    0x80 0x2821 0x0002 0x0000.00984d1c 0x00000000 0x0000.000.00000000 0x00000000   0x00000000 End dump data blocks tsn: 1 file#: 2 minblk 25 maxblk 25     没有为事务分配undo block.     再Dump表头 Start dump data blocks tsn: 9 file#: 9 minblk 395 maxblk 395 buffer tsn: 9 rdba: 0x0240018b (9/395) scn: 0x0000.00984d1c seq: 0x01 flg: 0x00 tail: 0x4d1c2301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0      spare2: 0      #extents: 28     #blocks: 1664                   last map 0x00000000 #maps: 0      offset: 2716       Highwater:: 0x0240018c ext#: 0      blk#: 3      ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0                    Unlocked -------------------------------------------------------- Low HighWater Mark :       Highwater:: 0x0240018c ext#: 0      blk#: 3      ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x02400189 Level 1 BMB for Low HWM block: 0x02400189 -------------------------------------------------------- Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0 L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x0240018a Last Level 1 BMB: 0x02400c8a Last Level II BMB: 0x0240018a Last Level III BMB: 0x00000000      Map Header:: next 0x00000000 #extents: 28   obj#: 30508 flag: 0x20000000 Extent Map -----------------------------------------------------------------    0x02400189 length: 8    0x024005d1 length: 8    0x024005d9 length: 8    0x024005e1 length: 8    0x024005e9 length: 8    0x024005f1 length: 8    0x024005f9 length: 8    0x02400601 length: 8    0x02400609 length: 8    0x02400611 length: 8    0x02400619 length: 8    0x02400621 length: 8    0x02400629 length: 8    0x02400631 length: 8    0x02400639 length: 8    0x02400641 length: 8    0x02400689 length: 128    0x02400709 length: 128    0x02400789 length: 128    0x02400809 length: 128    0x02400889 length: 128    0x02400909 length: 128    0x02400989 length: 128    0x02400a09 length: 128    0x02400a89 length: 128    0x02400b09 length: 128    0x02400c09 length: 128    0x02400c89 length: 128 Auxillary Map --------------------------------------------------------    Extent 0     : L1 dba: 0x02400189 Data dba: 0x0240018c    Extent 1     : L1 dba: 0x02400189 Data dba: 0x024005d1    Extent 2     : L1 dba: 0x024005d9 Data dba: 0x024005da    Extent 3     : L1 dba: 0x024005d9 Data dba: 0x024005e1    Extent 4     : L1 dba: 0x024005e9 Data dba: 0x024005ea    Extent 5     : L1 dba: 0x024005e9 Data dba: 0x024005f1    Extent 6     : L1 dba: 0x024005f9 Data dba: 0x024005fa    Extent 7     : L1 dba: 0x024005f9 Data dba: 0x02400601    Extent 8     : L1 dba: 0x02400609 Data dba: 0x0240060a    Extent 9     : L1 dba: 0x02400609 Data dba: 0x02400611
   Extent 10    : L1 dba: 0x02400619 Data dba: 0x0240061a    Extent 11    : L1 dba: 0x02400619 Data dba: 0x02400621    Extent 12    : L1 dba: 0x02400629 Data dba: 0x0240062a    Extent 13    : L1 dba: 0x02400629 Data dba: 0x02400631    Extent 14    : L1 dba: 0x02400639 Data dba: 0x0240063a    Extent 15    : L1 dba: 0x02400639 Data dba: 0x02400641    Extent 16    : L1 dba: 0x02400689 Data dba: 0x0240068b    Extent 17    : L1 dba: 0x02400709 Data dba: 0x0240070b    Extent 18    : L1 dba: 0x02400789 Data dba: 0x0240078b    Extent 19    : L1 dba: 0x02400809 Data dba: 0x0240080b    Extent 20    : L1 dba: 0x02400889 Data dba: 0x0240088b    Extent 21    : L1 dba: 0x02400909 Data dba: 0x0240090b    Extent 22    : L1 dba: 0x02400989 Data dba: 0x0240098b    Extent 23    : L1 dba: 0x02400a09 Data dba: 0x02400a0b    Extent 24    : L1 dba: 0x02400a89 Data dba: 0x02400a8b    Extent 25    : L1 dba: 0x02400b09 Data dba: 0x02400b0b    Extent 26    : L1 dba: 0x02400c09 Data dba: 0x02400c0b    Extent 27    : L1 dba: 0x02400c89 Data dba: 0x02400c8b --------------------------------------------------------    Second Level Bitmap block DBAs    --------------------------------------------------------    DBA 1:   0x0240018a     End dump data blocks tsn: 9 file#: 9 minblk 395 maxblk 395     发现High Water Mark 并没有提高。     此时，在其他session执行     SQL> insert into test select * from t where rownum<10;     该insert 被锁住     察看锁信息 SQL> select a.sid,b.sid,a.type,(select object_name from dba_objects where object _id=a.id1) object,a.lmode,b.request,a.block from v$lock a,v$Lock b where a.id1=b .id1 and a.id2=b.id2 and b.request>0 and a.block>0; Lock   Session       TYPE        OBJECT          LMODE    REQUEST      BLOCK
---------- ---------- -------------------- ---------- -------------------- ---------- ---------- 11 block 16           TM           TEST                     6               3                       1     Direct Load Data的时候在表上加了绝对锁（'Exclusive'），保证High Water Mark不被其他session修改；因High Water Mark不变，也就不会影响其他session一致读，也就不需要产生很多的undo。 SQL> truncate table test; Table truncated. SQL> insert /*+ append */ into test select * from t where rownum<10; 9 rows created. SQL> select * from test; select * from test           * ERROR at line 1: ORA-12838: cannot read/modify an object after modifying it in parallel SQL> delete from test; delete from test          * ERROR at line 1: ORA-12838: cannot read/modify an object after modifying it in parallel     同时这也是为什么当前session也无法对表进行查询和修改的原因。     3对临时段操作，如排序等。