• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    如何Shrink Undo表空间,释放过度占用的空间

    环境:
    OS:Red Hat Enterprise Linux AS release 4 (Nahant)
    DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

    一台Oracle10gR2数据库报出如下错误:
    ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
    ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
    ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
    ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
    ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX

    登陆检查,发现是SYSAUX表空间空间用尽,不能扩展,尝试手工扩展SYSAUX表空间:
     alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m
    Tue Nov 29 23:31:38 2005
    ORA-1237 signalled during: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m...

    出现ORA-1237错误,提示空间不足。这时候我才认识到是磁盘空间可能被用完了.

    是谁"偷偷的"用了那么多空间呢(本来有几十个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 ;
    (经测试,在9i环境下后面还要加上datafile '/opt/..../undotbs2.dbf' size 1024M)
    Tablespace created.
    4.切换UNDO表空间为新的UNDO表空间
    SQL> alter system set undo_tablespace=undotbs2 scope=both;

    System altered.

    创建了新的UNDO表空间以后,如果不知道系统使用的是pfile还是spfile文件,应使用参数both,会同时修改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;
    (本人经测试觉得其实是否可以用drop tablespace undotbs1 including contents and datafiles;直接连硬盘里面的dbf文件件一起删除)
    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

    空间已经释放。

    您可能感兴趣的文章:
    • Mysql在线回收undo表空间实战记录
    上一篇:ORACLE数据库空间整理心得
    下一篇:Linux系统下导出ORACLE数据库出现Exporting questionable statistics.错误 处理
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    如何Shrink Undo表空间,释放过度占用的空间 如何,Shrink,Undo,表,空间,