java思维

正在学习中：（

Oracle 回滚段空间回收步骤

 

是谁"偷偷的"用了那么多空间呢(本来有几十个G的Free磁盘空间的)?
检查数据库表空间占用空间情况:

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB

 2 from dba_data_files group by tablespace_name

 3 union all

 4 select tablespace_name,sum(bytes)/1024/1024/1024 GB

 5 from dba_temp_files group by tablespace_name order by GB;

TABLESPACE_NAME                        GB

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

USERS                          .004882813

UNDOTBS2                        .09765625

SYSTEM                         .478515625

SYSAUX                         .634765625

WAPCM_TS_VISIT_DETAIL            .9765625

HY_DS_DEFAULT                           1

MINT_TS_DEFAULT                         1

MMS_TS_DATA2                        1.375

MMS_IDX_SJH                             2

MMS_TS_DEFAULT                          2

IVRCN_TS_DATA                           2

TABLESPACE_NAME                        GB

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

MMS_TS_DATA1                            2

CM_TS_DEFAULT                           5

TEMP                           20.5498047

UNDOTBS1                       27.1582031

15 rows selected.


不幸的发现，UNDO表空间已经扩展至27G，而TEMP表空间也扩展至20G，这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。
显然曾经有大事务占用了大量的UNDO表空间和Temp表空间，Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性).
现在我们可以采用如下步骤回收UNDO空间:

1.确认文件

SQL> select file_name,bytes/1024/1024 from dba_data_files

 2 where tablespace_name like 'UNDOTBS1';

FILE_NAME

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

BYTES/1024/1024

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

+ORADG/danaly/datafile/undotbs1.265.600173875

          27810


2.检查UNDO Segment状态

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

         2          0            .071517944             .071517944          0

         3          0             .13722229              .13722229          0

         9          0            .236984253             .236984253          0

        10          0            .625144958             .625144958          0

         5          1            1.22946167             1.22946167          0

         8          0            1.27175903             1.27175903          0

         4          1            1.27895355             1.27895355          0

         7          0            1.56770325             1.56770325          0

         1          0            2.02474976             2.02474976          0

         6          0             2.9671936              2.9671936          0

11 rows selected.


3.创建新的UNDO表空间

SQL> create undo tablespace undotbs2;

Tablespace created.


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

空间已经释放。

posted on 2007-12-27 00:50 john 阅读(2242) 评论(1)  编辑  收藏 所属分类: Oracle Technology

评论

# re: Oracle 回滚段空间回收步骤 2007-12-27 09:17 jeasonzhao

实用啊,兄弟写的不错,受教了  回复  更多评论   


只有注册用户登录后才能发表评论。
网站导航: