以下操作不产生UNDO

最新推荐文章于 2024-07-10 21:10:26 发布

转载最新推荐文章于 2024-07-10 21:10:26 发布 · 719 阅读

135 篇文章

订阅专栏

回滚段表空间记录事务修改前的（旧的镜像）数据时，首先是在buffer cache中进行记录，然后会写入回滚段表空间数据文件，但是在还没有把这些数据提交到回滚段表空间数据文件时刻，数据库发生崩溃，那么数据库open后会首先前滚，根据redo信息生成undo中的那个前镜像数据，最后oracle在根据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。

3对临时段操作，如排序等。但对临时表操作仍然要产生undo（以前分析过，在我的blog里面）

http://blog.itpub.net/post/901/27280

from：http://www.itpub.net/thread-352280-1-1.html