UNDO 100%

另外查了下v$undostat，发现begin_time已经很久没有改变，
BEGIN_TIME           END_TIME             MAXQUERYLEN MAXCONCURRENCY UNEXPIREDBLKS EXPIREDBLKS

BEGIN_TIME DATE Identifies the beginning of the time interval

unexpired的在retention guarantee没有启用的情况下，可以看作是free的，你这里还有2g多的可用空间，99%看到的是假象

select sum(bytes),status from dba_undo_extents group by status;
select sid,USED_UBLK from v$transaction a,v$session b where a.addr=b.taddr;

dba_undo_extents:描述了extents包括在所有的undo表空间中的segments

SQL> select sum(bytes),status from dba_undo_extents group by status;

SUM(BYTES) STATUS
---------- ---------
   3670016 UNEXPIRED
1398407168 EXPIRED

 

查看UNDO表空间是否retention guarantee

undo_retention:指定事物commit后undo 将要保存的时间(秒)，在ORACLE10g中默认的是900秒。

GUARANTEE ： 保证undo_retention参数所设定的时间有效，这个是10g的新功能。

NOGUARANTEE:在没有guarantee的保证下，ORACLE并不能保证能够将undo信息存储900秒，如果undo表空间不足，那么ORACLE将忽略undo_retention的设置，直接覆盖掉以前的undo.

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

SQL> select tablespace_name,retention from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME         RETENTION
------------------------------ -----------
UNDOTBS1         NOGUARANTEE

RETENTION VARCHAR2(11)   Undo tablespace retention:
GUARANTEE - Tablespace is an undo tablespace with RETENTION specified as GUARANTEE
A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail.

NOGUARANTEE - Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE

SQL> select sum(bytes),status from dba_undo_extents group by status;

SUM(BYTES) STATUS
---------- ---------
   2621440 UNEXPIRED
  32702464 ACTIVE
1384775680 EXPIRED

SQL> select begin_time, END_TIME , MAXQUERYLEN,MAXCONCURRENCY,UNEXPIREDBLKS ,EXPIREDBLKS from v$undostat;

BEGIN_TIM END_TIME  MAXQUERYLEN MAXCONCURRENCY UNEXPIREDBLKS EXPIREDBLKS
--------- --------- ----------- -------------- ------------- -----------
26-FEB-01 26-FEB-01     124       3  3976   169216
26-FEB-01 26-FEB-01     725       0     0   170992
26-FEB-01 26-FEB-01     123       0     0   170992
26-FEB-01 26-FEB-01     725       0     0   170992
26-FEB-01 26-FEB-01    1326       0     0   170992

 

##################################################################################################################

SQL> select tablespace_name,retention from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME         RETENTION
------------------------------ -----------
UNDOTBS1         NOGUARANTEE

查看UNDO表空间是否retention guarantee

undo_retention:指定事物commit后undo 将要保存的时间(秒)，在ORACLE10g中默认的是900秒。

GUARANTEE ： 保证undo_retention参数所设定的时间有效，这个是10g的新功能。

NOGUARANTEE:在没有guarantee的保证下，ORACLE并不能保证能够将undo信息存储900秒，如果undo表空间不足，那么ORACLE将忽略undo_retention的设置，直接覆盖掉以前的undo.

查看实际的UNDO使用率:
select sum(bytes/1024/1024) as free_mb from dba_undo_extents where status in ('UNEXPIRED','EXPIRED');

SQL> select sum(bytes/1024/1024) as free_mb from dba_undo_extents where status in ('UNEXPIRED','EXPIRED');

   FREE_MB
----------
   2026.75

unexpired的在retention guarantee没有启用的情况下，可以看作是free的，你这里还有2g多的可用空间，99%看到的是假象

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3798035.html