UNDO表空间使用率一直保持100%

SQL> show parameter undo
NAME                   TYPE        VALUE
---------------------- ----------- ------------------------------
undo_management        string      AUTO
undo_retention         integer     900
undo_tablespace        string      UNDOTBS1

查看数据库undo空间一直保持100%

SELECT tablespace_name, file_id,
       100 * nvl((sum_alloc - nvl(sum_free, 0)) / 1024 / 1024 , 0) / (sum_alloc / 1024 / 1024) As per,
       nvl(sum_free, 0) / 1024 / 1024 as free,
       (sum_alloc - nvl(sum_free, 0)) / 1024 / 1024 as used,
       sum_alloc / 1024 / 1024 as max,
       file_name
  FROM (SELECT tablespace_name,
               file_id,
               sum(bytes) AS sum_alloc,
               sum(decode(maxbytes, 0, bytes, maxbytes)) AS sum_max,
               file_name
          FROM dba_data_files
         GROUP BY tablespace_name, file_name, file_id),
       (SELECT tablespace_name AS fs_ts_name,
               file_id as file_ts_id,
               sum(bytes) AS sum_free
          FROM dba_free_space
         GROUP BY tablespace_name, file_id)
 WHERE file_id = file_ts_id(+)
 order by 1, 2, 3;
查询结果如下：
7 UNDOTBS1 3 100 0 32767.984375 32767.984375 /u01/oradata/sss/undotbs01.dbf

通过查看undo空间的使用状态

SELECT TABLESPACE_NAME,
       STATUS,
       TRUNC(SUM(BLOCKS) * 8 / 1024) AS "SIZE M",
       COUNT(*) UNDO_EXTENT_NUM
  FROM DBA_UNDO_EXTENTS
 GROUP BY TABLESPACE_NAME, STATUS
 
结果显示大量的未过期信息占用了undo空间
1 UNDOTBS1 UNEXPIRED 32766 5907

查看导致UNDO空间被大量占用的语句

select BEGIN_TIME,
       END_TIME,

       MAXQUERYLEN,

       MAXQUERYSQLID,

       TUNED_UNDORETENTION

  from DBA_HIST_UNDOSTAT

MAXQUERYLEN 最长的查询时间
TUNED_UNDORETENTION 自动调整后的UNDORETENTION值

2016/2/10 4:45:29 2016/2/10 4:55:29 855 0rc4km05kgzb9 1575
2016/2/10 4:35:29 2016/2/10 4:45:29 255 0rc4km05kgzb9 975
2016/2/10 4:25:29 2016/2/10 4:35:29 859 0rc4km05kgzb9 1579
2016/2/10 4:15:29 2016/2/10 4:25:29 258 0rc4km05kgzb9 978
2016/2/10 4:05:29 2016/2/10 4:15:29 861 0rc4km05kgzb9 1582
2016/2/10 3:55:29 2016/2/10 4:05:29 260 0rc4km05kgzb9 981
2016/2/10 3:45:29 2016/2/10 3:55:29 864 0rc4km05kgzb9 1584
2016/2/10 3:35:29 2016/2/10 3:45:29 263 0rc4km05kgzb9 984
2016/2/10 3:25:29 2016/2/10 3:35:29 867 0rc4km05kgzb9 1587
2016/2/10 3:15:29 2016/2/10 3:25:29 267 0rc4km05kgzb9 987

因为是测试库，没有会话连接，只能判断为上面的语句占用了UNDO表空间且未释放。

SELECT A.USERNAME, B.NAME, C.USED_UBLK

  FROM V$SESSION A, V$ROLLNAME B, V$TRANSACTION C

 WHERE A.SADDR = C.SES_ADDR

   AND B.USN = C.XIDUSN;

通过和朋友讨论，我才注意到一个问题，就是我当前的系统时间是2015年3月份，而上面显示的时间超过了这个时间，这种情况一般是在初装系统的时候系统时间没有正确调整，在此期间产生了大量的UNDO信息还未释放时就把系统时间调整回来了，是否是因为这个原因（UNDO信息记录的是未来时间）。导致UNDO没法判断信息的保留时间，而无法回收UNDO呢？

SQL> select sysdate from dual;
SYSDATE
---------
09-MAR-15

我做个简单的测试

[root@021Y-SH-BKAP ~]# date -s "2016-03-09 11:55"
Wed Mar  9 11:55:00 CST 2016
[root@021Y-SH-BKAP ~]# su - oracle
[oracle@021Y-SH-BKAP ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 9 11:55:04 2016
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
Database opened.



SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-09 11:58:22