SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 13 02:10:21 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
---------- ---------- ---------
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 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
CUUG
更多oracle视频教程请点击:http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6
转载于:https://blog.51cto.com/19880614/1168276