oracle查看undo表空间⼤⼩，如何对undo表空间⼤⼩进⾏设置1.对于undo表空间⼤⼩的设置需要参考三组数据
(UR)每秒钟的undo_retention
(UPS)每秒钟⽣成的undo数据块的数量
(DBS)undo表空间的db_block_size⼤⼩
2.undo表空间需要分配空间⼤⼩，通过⼀下公式进⾏计算
其中两个配置可以在数据库中直接查询或配置：undo_retention和db_block_size，这个公式的第三部分需要到正在运⾏的数据库中进⾏查询，每秒钟使⽤的undo数据块可以从v$undostat视图中进⾏获取。
UndoSpace = UR * (UPS * DBS)
3.下⾯的公式计算每秒使⽤的峰值undo块
ENT_TIME和BEGIN_TIME是时间数据类型，ENT_TIME减去BEGIN_TIME，就是这两个时间段的天数，将天数转换为秒数需要乘以86400，⼀天的描述(24*60*60)
SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks= (SELECT MAX(undoblks) FROM v$undostat);
4.下⾯的查询计算处理峰值undo活动所需的字节数
SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
oracle 时间转换WHERE name = 'undo_tablespace'));
5.对于10G以上的版本使⽤下⾯的SQL语句进程查询
SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'undo_tablespace'));
6.下⾯对实际⽣产库进⾏查询检验
SQL> set num 15
SQL> SELECT (UR * (UPS * DBS)) AS "Bytes"
2 FROM (select max(tuned_undoretention) AS UR from v$undostat),
3 (SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
4 FROM v$undostat
5 WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
6 (SELECT block_size AS DBS
7 FROM dba_tablespaces
8 WHERE tablespace_name =
9 (SELECT UPPER(value)
10 FROM v$parameter
11 WHERE name = 'undo_tablespace'));
Bytes
---------------
63436368964.267
SQL> select 63436368964/1024/1024/1024 from dual;
63436368964/1024/1024/1024
--------------------------
59.0797*******
查看undo表空间的⼤⼩
SQL> select sum(bytes/1024/1024/1024) "undo sizeG" from dba_data_files where TABLESPACE_NAME='UNDOTBS1'; undo sizeG
---------------
39.9462890625
经过上⾯的对⽐，发现实际分配的undo表空间⽐计算得到的空间⼤⼩差距较⼤，alert⽇志中最近⼀定存在ORA-01555错误
Tue Apr 24 15:41:44 2018
ORA-01555 caused by SQL statement below (SQL ID: 7ng6c01huuk1s, Query Duration=6 sec, SCN: 0x0022.1c41386c): Tue Apr 24 15:41:44 2018
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论