服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Mssql|Oracle|Redis|

服务器之家 - 数据库 - Oracle - 如何Shrink Undo表空间,释放过度占用的空间

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

2019-10-27 16:24数据库之家 Oracle

如何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来察看空间占用情况.
[[email protected] ~]$ export ORACLE_SID=+ASM
[[email protected] ~]$ asmcmd
ASMCMD> du 
Used_MB      Mirror_used_MB
  21625               21625
ASMCMD> exit

空间已经释放。

延伸 · 阅读

精彩推荐
  • OracleOracle 数据表分区的策略

    Oracle 数据表分区的策略

    本文描述通过统计分析出医院信息系统需分区的表,对需分区的表选择分区键,即找出包括在你的分区键中的列(表的属性),对大型数据的管理比较有意义...

    Oracle教程网3262019-10-26
  • OracleOracle Portal及其门户网站开发概述

    Oracle Portal及其门户网站开发概述

    Portal是IT领域的新技术,是企业信息化工作的发展方向之一。本文首先介绍了Oracle Portal的定义、特点,接着阐述了portal的体系结构。随后本文介绍了Oracle...

    Oracle教程网2372019-10-26
  • OracleOracle数据操作和控制语言详解

    Oracle数据操作和控制语言详解

    SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。...

    Oracle教程网2322019-10-25
  • OracleOracle9i数据库异常关闭后的启动

    Oracle9i数据库异常关闭后的启动

    Oracle9i数据库异常关闭后的启动。Oracle shutdown的时候突然断电,导致使用sql/plus启动时无法连接到数据库,具体描述为: connection can not permitted, shut in progr...

    Oracle教程网4972019-10-22
  • OracleOracle 数据库操作技巧集

    Oracle 数据库操作技巧集

    提起 数据库 ,第一个想到的公司,一般都会是 Oracle (即甲骨文公司)。Oracle在数据库领域一直处于领先地位。Oracle关系数据库系统是目前世界上流行的关系...

    Oracle教程网3752019-10-24
  • OracleOracle9iPL/SQL编程的经验小结

    Oracle9iPL/SQL编程的经验小结

    平时在PL/SQL中的编程中遇到一些问题,这里以问答的形式来进行把它们总结下来,以供大家分享。 1、当需要向表中装载大量的数据流或者需要处理大量的...

    Oracle技术网2382019-10-23
  • OracleOracle动态交叉表生成

    Oracle动态交叉表生成

    Oracle是应用最广的大型数据库,而在范式下进行Oracle数据库设计则可以大大减少数据冗余,使数据库维护更方便,可惜范式下的数据表一般不能直接输出。...

    Oracle教程网1962019-10-21
  • Oracleoracle初始化参数设置

    oracle初始化参数设置

    oracle初始化参数设置 ...

    oracle技术网2422019-10-21
北京塞车全天计划精准版 中国期货配资网 新疆25选7开奖公告 广东快乐10分app 辽宁快乐12选5遗 炒股软件 福建36选7今天开奖结果 9.11上证指数 广东36选7走势图2元网 新疆11选5直播 投资人的股权分配 云南快乐10分走势图 幸运飞艇计划群推荐 福彩快乐十分玩法 河南快三开奖结果走势 河北11选5开奖走势 股票分析报告模板