管理UNDO（三）--回滚段的监控

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 13 02:10:21 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

02:10:21 SQL> conn scott/tiger
Connected.
02:10:31 SQL> insert into t01 select * from t01;

16 rows created.

02:10:39 SQL> /

32 rows created.

02:10:40 SQL> /

64 rows created.

1）查看会话信息：
02:11:48 SQL> select username,sid,serial#,saddr from v$session
02:12:10 2 where username is not null;

USERNAME                              SID    SERIAL# SADDR
------------------------------ ---------- ---------- --------
SYS                                    28         53 329537EC
SYS                                    21         42 3294B500
SYS                                    24         78 3294ED1C
SCOTT                                  20         19 3294A24C
SYS                                    22         58 3294C7B4

2）查看事务

02:11:44 SQL> select ADDR ,XIDUSN ,XIDSLOT,UBABLK ,STATUS ,SES_ADDR from v$transaction;

ADDR         XIDUSN    XIDSLOT     UBABLK STATUS           SES_ADDR
-------- ---------- ---------- ---------- ---------------- --------
31EC697C          7         18       4807 ACTIVE           3294A24C
31ECFE74         14          7        478 ACTIVE           3294B500

注释：
xidusn ：事务回滚段的id号
XIDSLOT：事务分配的事务槽
UBABLK：数据块的id
UBAFIL：文件id

3）查看会话所创建的事务

02:13:13 SQL> select a.xidusn,a.xidslot,a.UBABLK,a.STATUS,a.ses_addr ,b.username,b.sid,b.serial# from v$transaction a,v$session b
02:13:15 2 where a.ses_addr=b.saddr;

    XIDUSN    XIDSLOT     UBABLK STATUS           SES_ADDR USERNAME                              SID    SERIAL#
---------- ---------- ---------- ---------------- -------- ------------------------------ ---------- ----------
         7         18       4807 ACTIVE           3294A24C SCOTT                                  20         19
        14          7        478 ACTIVE           3294B500 SYS                                    21         42

4）查看事务分配的回滚段

02:10:11 SQL> select a.usn ,a.name,b.extents,b.xacts,b.writes from v$rollname a ,v$rollstat b
where a.usn=b.usn;

       USN NAME                              EXTENTS      XACTS     WRITES
---------- ------------------------------ ---------- ---------- ----------
         0 SYSTEM                                  5          0      14986
        14 _SYSSMU14$                              2          1      58962   （system roll segments）
         1 _SYSSMU1$                              22          0      94998
         2 _SYSSMU2$                               5          0      97956
         3 _SYSSMU3$                              85          0       5264
         4 _SYSSMU4$                               6          0       2312
         5 _SYSSMU5$                              16          0       2060
         6 _SYSSMU6$                              24          0      12296
         7 _SYSSMU7$                              24          1       5384
         8 _SYSSMU8$                              16          0      11090
         9 _SYSSMU9$                              40          0       2248
        10 _SYSSMU10$                             14          0       2060

12 rows selected.

5）查看回滚段中区的分配及数据块的状态（事务提交前）

02:17:17 SQL> select extent_id,bytes,status from dba_undo_extents
02:17:19 2 where segment_name like '%SYSSMU7$';

EXTENT_ID      BYTES STATUS
---------- ---------- ---------
         0      65536 EXPIRED
         1      65536 EXPIRED
         2      65536 EXPIRED
         3      65536 EXPIRED
         4      65536 EXPIRED
         5      65536 EXPIRED
         6      65536 EXPIRED
         7      65536 EXPIRED
         8      65536 EXPIRED
         9    1048576 EXPIRED
        10      65536 EXPIRED
        11      65536 EXPIRED
        12      65536 EXPIRED
        13      65536 EXPIRED
        14      65536 EXPIRED
        15      65536 EXPIRED
        16    1048576 EXPIRED

EXTENT_ID      BYTES STATUS
---------- ---------- ---------
        17    1048576 EXPIRED
        18    1048576 EXPIRED
        19    1048576 EXPIRED
        20    1048576 EXPIRED
        21    1048576 ACTIVE
        22    1048576 EXPIRED
        23    1048576 EXPIRED

24 rows selected.

02:10:40 SQL> commit;

Commit complete.

02:18:19 SQL>

事务提交后的状态

02:17:28 SQL> /

EXTENT_ID      BYTES STATUS
---------- ---------- ---------
        17    1048576 EXPIRED
        18    1048576 EXPIRED
        19    1048576 EXPIRED
        20    1048576 EXPIRED
        21    1048576 EXPIRED
        22    1048576 EXPIRED
        23    1048576 EXPIRED

24 rows selected.

13:25:05 SQL> select a.name,b.extents,b.rssize,b.xacts,b.waits,b.gets,optsize ,status
13:25:08 2 from v$rollname a, v$rollstat b
13:25:12 3 where a.usn=b.usn;

NAME                              EXTENTS     RSSIZE      XACTS      WAITS       GETS    OPTSIZE STATUS
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------------
SYSTEM                                  6     385024          0          0         72            ONLINE
_SYSSMU1$                               6    2351104          0          0        554            ONLINE
_SYSSMU2$                               5     319488          0          0        466            ONLINE
_SYSSMU3$                               3     188416          0          0        441            ONLINE
_SYSSMU4$                               5    3268608          0          0        447            ONLINE
_SYSSMU5$                               4     253952          0          0        468            ONLINE
_SYSSMU6$                              11    2678784          0          0        495            ONLINE
_SYSSMU7$                               4     253952          0          0        472            ONLINE
_SYSSMU8$                               4     253952          0          0        495            ONLINE
_SYSSMU9$                               5     319488          0          0        483            ONLINE
_SYSSMU10$                              4     253952          0          0        495            ONLINE

相关文章

[上一篇] 管理UNDO（二）--监视UNDO表空间