1.查看oracle undo segment段的信息
https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
SELECT T1.USN,
T2.NAME,
T1.STATUS,
T1.LATCH,
T1.EXTENTS,
T1.WRAPS,
T1.EXTENDS
FROM V$ROLLSTAT T1, V$ROLLNAME T2
WHERE T1.USN = T2.USN;
USN NAME STATUS LATCH EXTENTS WRAPS EXTENDS
---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
0 SYSTEM ONLINE 0 7 0 0
1 _SYSSMU1_1261223759$ ONLINE 1 3 215 61
2 _SYSSMU2_27624015$ ONLINE 2 3 282 116
3 _SYSSMU3_2421748942$ ONLINE 3 3 266 115
4 _SYSSMU4_625702278$ ONLINE 4 4 237 75
5 _SYSSMU5_2101348960$ ONLINE 5 3 255 87
6 _SYSSMU6_813816332$ ONLINE 6 3 254 88
7 _SYSSMU7_2329891355$ ONLINE 7 3 736 599
8 _SYSSMU8_399776867$ ONLINE 8 4 325 145
9 _SYSSMU9_1692468413$ ONLINE 9 15 306 123
10 _SYSSMU10_930580995$ ONLINE 10 4 467 223
11 rows selected.
2.检查事务使用undo segment的情况https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
https://www.cndba.cn/hbhe0316/article/4961
SELECT s.username,
s.sid,
pr.PID,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
rs.segment_id,
r.usn,
rs.segment_name,
r.rssize/1024/1024,
sq.sql_text
FROM gv$transaction t, gv$session s, gv$rollstat r, dba_rollback_segs rs ,gv$sqltext sq,gv$process pr
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
AND s.sql_address=sq.address
AND s.sql_hash_value = sq.hash_value
AND s.PADDR=pr.ADDR
ORDER BY t.used_ublk DESC ,sq.PIECE;
版权声明:本文为博主原创文章,未经博主允许不得转载。
oracle,linux