1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
| set num 30
SELECT DISTINCT STATUS,SUM(BYTES)/1024/1024,COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
select tablespace_name,
round(sum(case
when status = 'EXPIRED' then
blocks
end) * 8 /1024/1024,2) expired_gb,
round(sum(case
when status in ('ACTIVE') then
blocks
end) * 8 /1024/1024,2) active_gb,
round(sum(case
when status in ('UNEXPIRED') then
blocks
end) * 8 /1024/1024,2) unexpired_gb
from dba_undo_extents
where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED')
group by tablespace_name;
TABLESPACE_NAME EXPIRED_GB ACTIVE_GB UNEXPIRED_GB
------------------------------ ---------- ---------- ------------
UNDOTBS1 0 125.74
UNDOTBS2 0 56.58
SELECT tablespace_name, used_space_mb, total_space_mb, used_space_percent
FROM (SELECT tablespace_name, ROUND(SUM(bytes)/(1024*1024),2) used_space_mb,
ROUND(SUM(maxbytes)/(1024*1024),2) total_space_mb,
ROUND(100*SUM(bytes)/SUM(maxbytes),2) used_space_percent
FROM dba_data_files
WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO')
GROUP BY tablespace_name)
ORDER BY used_space_percent DESC;
TABLESPACE_NAME USED_SPACE_MB TOTAL_SPACE_MB USED_SPACE_PERCENT
------------------------------ ------------- -------------- ------------------
UNDOTBS02 10240 32767.98 31.25
UNDOTBS01 10240 32767.98 31.25
---查询 UNDO 表空间的基本信息
set linesize 300 pagesize 300
col file_name for a50
SELECT ts.tablespace_name, ts.status, df.file_name, df.bytes / 1024 / 1024 AS "Size (MB)"
FROM dba_tablespaces ts
JOIN dba_data_files df ON ts.tablespace_name = df.tablespace_name
WHERE ts.contents = 'UNDO';
---查看活跃事务占用undo情况
SELECT
s.sid,
s.serial#,
t.used_ublk,
t.start_time
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE s.status = 'ACTIVE';
The Expired blocks will be reused and hence this should be counted as 'available' space in the Undo segment.
You can check the status of the undo extents via the SQLs below.
select sum(bytes /(1024*1024)) from dba_undo_extents where status='EXPIRED';
select sum(bytes /(1024*1024)) from dba_undo_extents where status='ACTIVE';
select sum(bytes /(1024*1024)) from dba_undo_extents where status='UNEXPIRED';
For more detailed information, please refer to：
Master Note: High Undo Space Usage ( Doc ID 1578639.1 )
---检查undo表空间文件是否自动扩展
SET linesize 1000 pagesize 1000
COL FILE_NAME FOR A50
COL TABLESPACE_NAME FOR A50
COL AUTOEXTENSIBLE FOR A15
COL STATUS FOR A10
COL MAXBYTES FOR A15
select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE,STATUS,MAXBYTES/1024/1024/1024 MAX_GB,USER_BYTES/1024/1024/1024 USER_GB from dba_data_files where tablespace_name like '%UNDO%';
---计算过去7*24小时undo平均使用量
set linesize 1000 pagesize 1000
col undo_retention for a50
col db_block_size for a50
select ur undo_retention,
dbs db_block_size,
((ur * (ups * dbs)) + (dbs * 24))/1024 /1024 as "used(MB)"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
from v$undostat),
(select value as dbs from v$parameter where name = 'db_block_size');
UNDO_RETENTION DB_BLOCK_SIZE used(MB)
-------------------------------------------------- -------------------------------------------------- ------------------------------
36000 8192 43560.817242874279604662685477
|
