oracle 回滚空间查询,Oracle回滚段空间回收步骤

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

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

此处使用spfile需要注意，以前曾经记录过这样一个案例:Oracle诊断案例-Spfile案例一则

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

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

2 from v$rollstat order by rssize;

USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

---------- ---------- --------------- --------------------- ---------------------- ----------

14          0 ONLINE                     .000114441             .000114441          0

19          0 ONLINE                     .000114441             .000114441          0

11          0 ONLINE                     .000114441             .000114441          0

12          0 ONLINE                     .000114441             .000114441          0

13          0 ONLINE                     .000114441             .000114441          0

20          0 ONLINE                     .000114441             .000114441          0

15          1 ONLINE                     .000114441             .000114441          0

16          0 ONLINE                     .000114441             .000114441          0

17          0 ONLINE                     .000114441             .000114441          0

18          0 ONLINE                     .000114441             .000114441          0

0          0 ONLINE                     .000358582             .000358582          0

USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

---------- ---------- --------------- --------------------- ---------------------- ----------

6          0 PENDING OFFLINE             2.9671936              2.9671936          0

12 rows selected.

再看:

11:32:11 SQL> /

USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

---------- ---------- --------------- --------------------- ---------------------- ----------

15          1 ONLINE                     .000114441             .000114441          0

11          0 ONLINE                     .000114441             .000114441          0

12          0 ONLINE                     .000114441             .000114441          0

13          0 ONLINE                     .000114441             .000114441          0

14          0 ONLINE                     .000114441             .000114441          0

20          0 ONLINE                     .000114441             .000114441          0

16          0 ONLINE                     .000114441             .000114441          0

17          0 ONLINE                     .000114441             .000114441          0

18          0 ONLINE                     .000114441             .000114441          0

19          0 ONLINE                     .000114441             .000114441          0

0          0 ONLINE                     .000358582             .000358582          0

11 rows selected.

Elapsed: 00:00:00.00

6.删除原UNDO表空间

11:34:00 SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

Elapsed: 00:00:03.13

7.检查空间情况

由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.

[oracle@danaly ~]$ export ORACLE_SID=+ASM

[oracle@danaly ~]$ asmcmd

ASMCMD> du

Used_MB      Mirror_used_MB

21625               21625

ASMCMD> exit

空间已经释放。