监控Undo表空间

1:查看所有的Undo表空间

SELECT * FROM dba_tablespaces where contents='UNDO'

2:Undo大小计算公式：

SELECT ((UR * (UPS * DBS)) + (DBS * 24))/1048576 AS "MB"
FROM (SELECT value AS UR
FROM v$parameter
WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM
(((end_time-begin_time)*86400))) AS UPS
FROM v$undostat),
(SELECT value AS DBS
FROM v$parameter
WHERE name = 'db_block_size');
3：修改Undo表空间大小

 先删除原来的表空间：
 drop tablespace UNDOTBS2 including contents and datafiles
 再建立一个和原来名称一样的表空间
 create undo tablespace UNDOTBS2 datafile '/home/oracle/oradata/depsight/undotbs2.dbf' size 50m