数据库版本:10.2.0.4 操作系统: AIX6.1 数据文件为普通文件系统文件
现在的问题是: UNDOTBS空间一直增长,从5g增长到8G到10G到14G ,现在使用率又在98%
UNDOTBS空间在10天前曾重建,新建UNDOTBS后,使用如下命令删除老的UNDO数据文件
drop tablespace UNDOTBS1 including contents and datafiles
但执行完成后,数据文件不存在了,但空间发现没有释放 使用lsof也无法找到相关进程
现在的UNDO空间为14G,如下为一些信息,有10G空间没有过期,但检查V$TRANSACTION发现没有事务
SQL> select sum(bytes),status from dba_undo_extents group by status;
SUM(BYTES) STATUS
---------- ---------
4248043520 EXPIRED
1.0690E+10 UNEXPIRED
SQL> select s.username, u.name from v$transaction t,v$rollstat r,
2 v$rollname u,v$session s where s.taddr=t.addr and
3 t.xidusn=r.usn and r.usn=u.usn order by s.username;
no rows selected
SQL> select count(*) from v$transaction;
COUNT(*)
----------
0
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0 0 .000358582 .000358582 0
43 0 .363273621 .378898621 13
40 0 .385856628 .424919128 20
41 0 .412651062 .412651062 16
39 0 .418144226 .437675476 12
42 0 .45287323 .46068573 13
38 0 .511833191 .519645691 17
45 0 .558769226 .577323914 13
44 0 .622550964 1.32433319 28
46 0 .753105164 .753105164 13
1 0 1.96685028 1.96685028 6
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
2 0 3.50597382 3.50597382 36