实验细节：
1 开始一个事务
ZXC@trade>select * from tt;
--
ID NAME
---------- --------------------
4 tttt
1 xxx
2 rrrr
3 kkkk
--
ZXC@trade>update tt set name='hhh' where id=2;
1 row updated.
此事务不要commit或者rollback
2 查看事务信息
a)查看自身sid
select userenv('SID') from dual;
select sid from v$mystat where rownum=1;
b)获取undo槽位等信息：
ZXC@trade>select xidusn,xidslot,xidsqn from V$TRANSACTION t,v$session s where s.sid=9 and s.taddr=t.addr;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 17 990
--
ZXC@trade>select sid,trunc(id1/65536) usn,mod(id1,65536) slot,id2 wrap,lmode from v$lock where type='TX' and sid='9';
SID USN SLOT WRAP LMODE
---------- ---------- ---------- ---------- ----------
9 3 17 990 6
--
其中
Column Description
XIDUSN Undo segment number
XIDSLOT Slot number
XIDSQN Sequence number
3 根据查询到的信息找到undo块：
a)查看undo块头位置
SYS@trade>col name for a30
SYS@trade>select * from v$rollname where usn=3;
USN NAME
---------- ------------------------------
- 3 _SYSSMU3_1723003836$
b)dump undo块
alter system dump undo header"_SYSSMU3_1723003836$";
select * from v$diag_info where name='Default Trace File';
c)查看undo块头信息：
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x03de 0x0002 0x0000.00131fae 0x00c000ea 0x0000.000.00000000 0x00000001 0x00000000 1642738223
0x0f 9 0x00 0x03dd 0x0008 0x0000.00131f03 0x00c000ee 0x0000.000.00000000 0x00000001 0x00000000 1642737883
0x10 9 0x00 0x03de 0x0004 0x0000.0013201c 0x00c000ef 0x0000.000.00000000 0x00000001 0x00000000 1642738223
0x11 10 0x80 0x03de 0x0001 0x0000.001318e5 0x00c000ef 0x0000.000.00000000 0x00000001 0x00000000 0
0x12 9 0x00 0x03dd 0x0006 0x0000.00131fa1 0x00c000ea 0x0000.000.00000000 0x00000001 0x00000000 164273822
可以看到找到了对应的事务
state为10说明为活动事务，warp#为0x03de(16进制)同v$lock查到的信息相同，换算成10进制为990。
4、检查undo活跃的块：
未提交第1部的update语句，查看，发现active有一个是活跃的。
SYS@trade>SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*),TABLESPACE_NAME FROM DBA_UNDO_EXTENTS GROUP BY STATUS,TABLESPACE_NAME;
STATUS SUM(BYTES) COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED 4784128 28 UNDOTBS1
UNEXPIRED 5308416 21 UNDOTBS1
ACTIVE 65536 1 UNDOTBS1
提交第一个未update的语句后，再次检查，发现active状态的已经没有了。
SYS@trade>/
STATUS SUM(BYTES) COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED 4784128 28 UNDOTBS1
UNEXPIRED 5373952 22 UNDOTBS1
5、检查undo表空间：
SYS@trade>select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
2 round(((a.bytes-b.bytes)/a.bytes)*100,2)||'%' "percent_used"
3 from
4 (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
5 (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name
7 order by ((a.bytes-b.bytes)/a.bytes) desc;
TABLESPACE_NAME Sum MB used MB free MB percent_used
------------------------------ ---------- ---------- ---------- -----------------------------------------
SYSTEM 740 738.0625 1.9375 99.74%
UNDOTBS1 80 79 1 98.75%
SYSAUX 610 573.375 36.625 94%
USERS 35 32.8125 2.1875 93.75%
创建一张表zxc,并插入30万数据，查询大小
create table zxc as
select to_date('01-AUG-2017')+trunc(dbms_random.value(0,7)) dte,
rpad('padding',20) padding
from dual
connect by level <= 300000;
insert into zxc
select to_date('08-AUG-2017')+trunc(dbms_random.value(0,7)) dte,
rpad('padding',20) padding
from dual
connect by level <= 300000;
插入30万行未提交前，查询
SYS@trade>SELECT DISTINCT STATUS, SUM(BYTES/1024/1024) sum_m, COUNT(*),TABLESPACE_NAME
2 FROM DBA_UNDO_EXTENTS GROUP BY STATUS,TABLESPACE_NAME;
STATUS SUM_M COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED .75 12 UNDOTBS1
UNEXPIRED 76.9375 151 UNDOTBS1
ACTIVE 1.3125 6 UNDOTBS1
插入30万行提交后，查询
SYS@trade>/
STATUS SUM_M COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED .75 12 UNDOTBS1
UNEXPIRED 78.25 157 UNDOTBS1
SYS@trade>
可以看到处于active状态的都变成UNEXPIRED的状态了。
再等一会会转换为expired过期状态的undo：
SYS@trade>/
STATUS SUM_M COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED 71.8125 144 UNDOTBS1
UNEXPIRED 7.1875 25 UNDOTBS1
note：这里我将undo_retention设置为60s了：
SYS@trade>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 60
undo_tablespace string UNDOTBS1
接着将zxc表中60万数据全部删除，并不commit
ZXC@trade>delete from zxc;
600000 rows deleted.
SYS@trade>SELECT DISTINCT STATUS, SUM(BYTES/1024/1024) sum_m, COUNT(*),TABLESPACE_NAME
2 FROM DBA_UNDO_EXTENTS GROUP BY STATUS,TABLESPACE_NAME;
STATUS SUM_M COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED .75 12 UNDOTBS1
UNEXPIRED 7.25 26 UNDOTBS1
ACTIVE 79 143 UNDOTBS1
此时undo表空间如下：
SYS@trade>/
TABLESPACE_NAME TOTAL_m FREE_m USE_m USE
------------------------------ ----------- ----------- ----------- -------
SYSAUX 610.000 35.563 574.438 94.17%
SYSTEM 740.000 1.938 738.063 99.74%
TEMP 29.000 1.000 28.000 96.55%
UNDOTBS1 90.000 2.000 88.000 97.78%
USERS 58.750 2.938 55.813 95.00%
提交后：
SYS@trade>SELECT DISTINCT STATUS, SUM(BYTES/1024/1024) sum_m, COUNT(*),TABLESPACE_NAME
2 FROM DBA_UNDO_EXTENTS GROUP BY STATUS,TABLESPACE_NAME;
STATUS SUM_M COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED 3.4375 25 UNDOTBS1
UNEXPIRED 82.4375 153 UNDOTBS1
undo表空间大小
SYS@trade>/
TABLESPACE_NAME TOTAL_BYTES FREE_BYTES USE_BYTES USE
------------------------------ ----------- ----------- ----------- -------
SYSAUX 630.000 31.625 598.375 94.98%
SYSTEM 740.000 1.938 738.063 99.74%
TEMP 29.000 1.000 28.000 96.55%
UNDOTBS1 100.000 13.125 86.875 86.88%
USERS 58.750 2.938 55.813 95.00%
SYS@trade>
---------------通过回滚段找到占用回滚段对应的sql；
再次插入100万数据
insert into zxc
select to_date('08-AUG-2017')+trunc(dbms_random.value(0,7)) dte,
rpad('padding',20) padding
from dual
connect by level <=1000000;
commit;
然后提交
接着删除10万数据先不提交
delete from zxc where DTE='14-AUG-17';
检查undo表空间：
SYS@trade>/
TABLESPACE_NAME TOTAL_BYTES FREE_BYTES USE_BYTES USE
------------------------------ ----------- ----------- ----------- -------
SYSAUX 630.000 31.438 598.563 95.01%
SYSTEM 740.000 1.938 738.063 99.74%
TEMP 29.000 1.000 28.000 96.55%
UNDOTBS1 100.000 .250 99.750 99.75%
USERS 75.000 4.188 70.813 94.42%
检查undo段数据量：
SYS@trade>/
STATUS SUM_M COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED 64.625 130 UNDOTBS1
UNEXPIRED 14.75 34 UNDOTBS1
ACTIVE 19.375 55 UNDOTBS1
查找造成undo增加的sql语句：
SYS@trade>select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb
2 From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s
3 Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr
4 order by segment_name ;
SID SERIAL# SQL_ID USN SEGMENT_NAME STATUS MB
---------- ---------- ---------------- ---------- ------------------------------ ---------- ----------
197 867 3 _SYSSMU3_1723003836$ ONLINE 19.3671875
9 545 8 _SYSSMU8_517538920$ ONLINE 1.1171875
SYS@trade>col sql_text for a20
SYS@trade>select distinct s.machine,s.program,s.sid,round(t.used_ublk*8/1024,2) undo_MB,used_urec undo_records,s.status,l.sql_text
2 from v$transaction t,gv$session s,v$sqlstats l
3 where t.ses_addr=s.saddr
4 and s.sql_id=l.sql_id(+) order by undo_MB;
MACHINE PROGRAM SID UNDO_MB UNDO_RECORDS STATUS SQL_TEXT
---------------- -------------------- ---------- ---------- ------------ ---------- --------------------
yictdbtest1 sqlplus@yictdbtest1 9 .01 1 INACTIVE
(TNS V1-V3)
yictdbtest1 sqlplus@yictdbtest1 197 18.52 142306 INACTIVE delete from zxc wher
(TNS V1-V3) e DTE='14-AUG-17'
参考：
http://blog.itpub.net/29135257/list/3/
扫一扫
09-07
522
522
05-15
952
952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
