磁盘空间不足(三)重建undo表空间

有的时候导致磁盘空间不足的是因为undo表空间增长迅速导致的，这个时候我们可能需要通过重建undo表空间来shrinkundo表空间，下面介绍一下操作步凑：

1. 检查数据库占用情况

如果发现undo表空间占用很大，那么这里就需要shrink undo表空间。

2. 确认shrink的undo表空间 

3. 检查UNDO Segment状态

SQL> SELECT USN,
  2         XACTS,
  3         RSSIZE / 1024 / 1024 / 1024,
  4         HWMSIZE / 1024 / 1024 / 1024,
  5         status,
  6         SHRINKS
  7  FROM   V$ROLLSTAT
  8  ORDER  BY RSSIZE;
 
       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 STATUS             SHRINKS
---------- ---------- --------------------- ---------------------- --------------- ----------
         0          0   0.00035858154296875    0.00035858154296875 ONLINE                   0
         2          0   0.00109100341796875      0.796989440917969 ONLINE                  66
         3          0   0.00109100341796875      0.211051940917969 ONLINE                  35
        28          0   0.00109100341796875      0.257987976074219 ONLINE                  69
         1          0   0.00109100341796875     0.0782394409179688 ONLINE                  61
        26          0   0.00109100341796875      0.179801940917969 ONLINE                  46
        24          0   0.00109100341796875      0.179801940917969 ONLINE                  62
        19          1   0.00109100341796875      0.804801940917969 ONLINE                  70
        16          0   0.00109100341796875      0.218925476074219 ONLINE                  63
        15          0   0.00109100341796875      0.796989440917969 ONLINE                  81
        14          0   0.00109100341796875      0.801872253417969 ONLINE                  97
        13          0   0.00109100341796875      0.172966003417969 ONLINE                  49
        11          1   0.00109100341796875      0.164237976074219 ONLINE                  43
        10          0   0.00109100341796875      0.203239440917969 ONLINE                  62
         8          1   0.00109100341796875      0.234489440917969 ONLINE                  27
         6          0   0.00109100341796875      0.265739440917969 ONLINE                  71
         5          0   0.00109100341796875      0.336051940917969 ONLINE                  44
        25          0   0.00206756591796875      0.797111511230469 ONLINE                  70
        27          0   0.00206756591796875      0.130973815917969 ONLINE                  24
         7          0   0.00206756591796875      0.289299011230469 ONLINE                  55
 
       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 STATUS             SHRINKS
---------- ---------- --------------------- ---------------------- --------------- ----------
        18          0   0.00206756591796875      0.242301940917969 ONLINE                  41
        21          0   0.00206756591796875      0.211051940917969 ONLINE                  51
        22          0   0.00206756591796875      0.164176940917969 ONLINE                  47
         4          0   0.00402069091796875      0.812614440917969 ONLINE                  66
        23          0   0.00792694091796875      0.273551940917969 ONLINE                  33
        12          0   0.00890350341796875      0.265739440917969 ONLINE                  13
        17          0   0.00890350341796875      0.109489440917969 ONLINE                  53
         9          0   0.00890350341796875      0.171989440917969 ONLINE                  63
        20          0    0.0167160034179688      0.390739440917969 ONLINE                  69
 
29 rows selected
 

4. 创建新的UNDO表空间

5. 切换UNDO表空间为新的UNDO表空间

 

6. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE

    这里主要查看status的所有值为offline就表示所有的undo segement已经offline。
7. 删除原UNDO表空间

8. 检查空间情况
9. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
 select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;

 

附： v$rollback视图的解析说明

 

这个视图包括了Rollback segment 的统计信息.

---------------------------------------------
USN Rollback segment number
LATCH Latch for the rollback segment
EXTENTS Number of extents in the rollback segment
RSSIZE Size (in bytes) of the rollback segment. This value differs by the number
of bytes in one database block from the value of the BYTES column of the
ALL/DBA/USER_SEGMENTS views.
See Also: Oracle Database Administrator's Guide.
WRITES Number of bytes written to the rollback segment
XACTS Number of active transactions
GETS Number of header gets
WAITS Number of header waits
OPTSIZE Optimal size of the rollback segment
HWMSIZE High-watermark of rollback segment size
SHRINKS Number of times the size of a rollback segment decreases
WRAPS Number of times rollback segment is wrapped
EXTENDS Number of times rollback segment size is extended
AVESHRINK Average shrink size
AVEACTIVE Current size of active extents, averaged over time.
STATUS Rollback segment status:
ONLINE
PENDING OFFLINE
OFFLINE
FULL
CUREXT Current extent
CURBLK Current block