Oracle UNDO块

?1）首先更新几条数据，但是不进行commit如下：

[email?protected] prod>select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

[email?protected] prod>update scott.emp set sal=2000 where empno=‘7369‘;

1 row updated.

[email?protected] prod>update scott.emp set sal=2001 where empno=‘7499‘;

1 row updated.

[email?protected] prod>update scott.emp set sal=2002 where empno=‘7521‘;

1 row updated.

[email?protected] prod>update scott.emp set sal=2003 where empno=‘7566‘;

1 row updated.

?2）v$transaction列出活动事务相关信息

[email?protected] prod>select xidusn,xidslot,ubafil,ubablk from v$transaction;

    XIDUSN    XIDSLOT     UBAFIL     UBABLK
---------- ---------- ---------- ----------
        11         25          6         12

XIDUSN:Undo segment number
XIDSLOT:Slot number
UBAFIL:Undo block address(UBA) filenum
UBABLK:UBA block number
该事务使用的undo段号为11，该事务在undo的第25个槽位，数据文件号为6，使用的数据块为12；?

3）V$ROLLNAME列出所有在线回滚段。只能在数据库打开时访问。

[email?protected] prod>select * from v$rollname where usn=11;

       USN NAME
---------- ------------------------------
        11 _SYSSMU11_1796584641$?

NAME:Rollback segment name
undo段号为11，名字为?_SYSSMU11_1796584641$

4）V$ROLLSTAT包含回滚段信息。

[email?protected] prod>select usn,status,curext,xacts from v$rollstat;

       USN STATUS              CUREXT      XACTS
---------- --------------- ---------- ----------
         0 ONLINE                   1          0
        11 ONLINE                   0          1?

USN:Rollback segment number
STATUS:Rollback segment status:ONLINE/PENDING OFFLINE/OFFLINE/FULL
CUREXT:Current extent
XACTS:Number of active transactions
undo段号为6，在线，区号为3,1个活动事务

4）转储undo header

[email?protected] prod>alter system dump undo header ‘_SYSSMU11_1796584641$‘;

System altered.?

5）查看默认trace文件位置?

[email?protected] prod>col value for a50
[email?protected] prod>select * from v$diag_info;

   INST_ID NAME                                                             VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------
         1 Diag Enabled                                                     TRUE
         1 ADR Base                                                         /u01
         1 ADR Home                                                         /u01/diag/rdbms/prod/prod
         1 Diag Trace                                                       /u01/diag/rdbms/prod/prod/trace
         1 Diag Alert                                                       /u01/diag/rdbms/prod/prod/alert
         1 Diag Incident                                                    /u01/diag/rdbms/prod/prod/incident
         1 Diag Cdump                                                       /u01/diag/rdbms/prod/prod/cdump
         1 Health Monitor                                                   /u01/diag/rdbms/prod/prod/hm
         1 Default Trace File                                               /u01/diag/rdbms/prod/prod/trace/prod_ora_2361.trc
         1 Active Problem Count                                             0
         1 Active Incident Count                                            0

11 rows selected.?

6）分析UDNO段头块的日志?

more /u01/diag/rdbms/prod/prod/trace/prod_ora_2361.trc?

********************************************************************************
Undo Segment:  _SYSSMU11_1796584641$ (11)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 15    
                  last map  0x00000000  #maps: 0      offset: 4080  
      Highwater::  0x0180000e  ext#: 0      blk#: 5      ext size: 7     
  #blocks in seg. hdr‘s freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 0     
                   Unlocked
     Map Header:: next  0x00000000  #extents: 2    obj#: 0      flag: 0x40000000

• #extents: 2??表示11号UNDO段有两个区
• #blocks: 15? ?表示11号UNDO回滚段两个区中有15个UNDO BLOCK可用。（为什么不是16个UNDO BLOCK块呢，去掉一个UNDO段头块）
• ext#: 0? ?? ? 表示这个事务发生在第1个区（从0开始）
• blk#: 5 ?? ? 表示这个事务发生在第1个区的第5个块上。
• ext size: 7? ???表示1个区上有7个UNDO BLOCK可用

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!