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

最新推荐文章于 2021-04-09 06:27:14 发布
最新推荐文章于 2021-04-09 06:27:14 发布
阅读量2.6k 收藏
点赞数
分类专栏： Oracle数据库
登陆检查，发现是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;

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

空间已经释放。
huaishu
关注
专栏目录
Oracle收缩表，释放占用的存储空间（shrink space）
tttzzzqqq2018的博客
08-13 4363
收缩表（shrink space cascade），整理碎片，降低高水位。
oracle 实例表空间不足,shrink space遇到UNDO_TABLESPACE表空间不足现象
weixin_36280492的博客
04-15 522
很大的表。SHRINK TABLE确实要花费UNDO 。但是我没看到哪里说SHRINK就是INSERT DELETE噢，只是说compaction阶段会把数据分为小的包进行操作，避免大面积的堵塞。但是降低HWM的时候就是全表加X锁了。所以，分两部做看看。如果真的时UNDO不够那就没办法了，只有加。大表分分区嘛。这样操作耗用UNDO少点。。呵呵文档The compaction phase of a ...
如何Shrink Undo表空间, 释放过度占用的空间
Aamir 的专栏
04-27 1928
Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性). 现在我们可以采用如下步骤回收UNDO空间: 1.确认文件 SQL> select file_name,bytes/1024/1024 from dba_data_files   2  wher
释放TEMP表空间占用硬盘空间
08-21
如何释放TEMP表空间占用的大量硬盘空间。
释放UNDO表空间
wu_wei_jie的专栏
05-03 841
数据库的维护与数据库的编程实际应用中经常会遇到不解之处，对于大数据量做的DML操作后，我们是的把Oracle undo的表空间扩展到十几个G或是几十个G 但是这些表空间的所占用磁盘的物理空间又不会被Oracle 所释放，如果你用的是PC机很可能会遇到磁盘空间不足的问题。经过个人整理经过如下操作可以重构Oracle undo表空间，同样temp表空间也可能在你查询大数据或则创建索引的时候无限扩大导致磁盘空间不足，同样可以用如下方式解决此问题：查看各表空间名称 select name from v$tables
oracle怎么释放undo空间,如何回缩Undo表空间 释放过度占用空间
weixin_32301287的博客
04-07 1841
【IT168 技术文档】摘要：在使用Oracle9i/Oracle10g的自动Undo管理表空间(AUM)特性时，经常会因为各种原因而导致Undo表空间过度扩展。本文通过一则案例诊断，介绍如何收缩过度扩展的Undo表空间，释放占用空间。环境:OS: Red Hat Enterprise Linux AS release 4 (Nahant)DB: Oracle Database 10g Enter...
ORACLE数据库空间整理心得
09-11
我们将从Shrink Undo表空间开始，介绍如何释放过度占用的空间，并解决UNDO表空间中的问题。 1. Shrink Undo表空间 在ORACLE数据库中，Undo表空间是一个非常重要的组件，它用于存储事务的Undo信息。当Undo表空间变...
释放undo表空间
lizhiyu211的专栏
03-10 320
在日常的数据库维护和数据库编程中经常会遇到犹豫对大数据量做DML操作后是得ORACLE的undo表空间扩展到十几个G或者几十个G 但是这些表空间的所占用磁盘的物理空间又不会被oracle所释放，如果你用的是PC机很可能会遇到磁盘空间不足的问题，经过个人整理经过如下操作可以重构undo表空间，同样temp表空间也可能在你查询大数据或则创建索引的时候无限扩大导致磁盘空间不足，同样可以用如下方式解决此问...
ORACLE收缩表空间
热门推荐
BLACK的专栏
05-29 4万+
最近导一个空库到数据库后，发现占用的表空间非常大，执行表收缩（SHRINK SPACE CASCADE）后，发现实际占用的空间不到1%。 但是收缩表空间大小提示错误     ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 5000M;     提示：ORA-03297: file contains used data
管理undo表空间全面分析详解
04-21
oracle 数据库的 undo表空间管理 全面分析介绍
[转]Oracle HowTo:如何Shrink Undo表空间,释放过度占用的空间
空心居
12-28 1748
Oracle HowTo:如何Shrink Undo表空间,释放过度占用的空间作者：eygle 转自：http://www.eygle.com/archives/2005/11/oracle_howto_shrink_undo_tablespace.html 环境:OS:Red Hat Enterprise Linux AS release 4 (Nahant)DB:Oracle Da
oracle undo表空间释放
csdn_x000006的博客
05-20 295
oracle10G的undo表空间有Automatic Undo Retention Tuning的特性。 对于UNDO表空间的数据文件属性为autoextensible,则undo_retenion参数必须设置，UNDO信息...
oracle调整undo大小,oracle undo表空间爆满处理
weixin_36099503的博客
04-09 1296
undo 表空间使用案例分析(UNDOTBS1使用率100%，enq：US-contention,释放undo)转载 2013年02月20日 16:04:29 12007问题描述：近几天发现Oracle10.2.0.4数据库undo tablespace表空间使用率很高(最高时达到100%)，报警系统频繁报障undo tablespace表空间使用率过高。TABLESPACE_NAME ...
ORACLE undo表空间满查询占用sql
gaobudong1234的博客
10-12 9080
select start_time,  --事务起始时间          username,    --用户名          s.MACHINE,   --机器名称          s.OSUSER,    --登录名          r.name,      --回滚段名称          ubafil,      --Undo block address (UBA) fi
undo表空间
你学废了么的博客
03-24 2676
undo暴增后查询思路 1.查询undo active使用状态 select tablespace_name, status, round( sum( bytes ) / 1048576, 2 ) mb, count(*) extent_count from dba_undo_extents group by tablespace_name, status order by tablespace_name, status; UNEXPIRED已经释放，在保留期内。无需手动处理，在undo表空间紧张时，un
oracledelete后表空间释放
最新发布
06-28
### 回答1： 当使用Oracle的DELETE语句删除表中的数据时，表空间中的空间并不会立即释放。这是因为Oracle使用了一种称为Undo机制的技术来保证数据的一致性和事务的原子性。当删除操作执行时，Oracle会将删除的数据存储在Undo表空间中，以便在需要时可以恢复数据。因此，即使删除了数据，表空间中的空间也不会立即释放。 要释放表空间中的空间，可以使用Oracle的ALTER TABLESPACE语句来收缩表空间。该语句将会将表空间中的空闲空间收缩并返回给操作系统。收缩表空间的语法如下： ALTER TABLESPACE tablespace_name SHRINK SPACE; 其中，tablespace_name是要收缩的表空间的名称。执行该语句后，Oracle将会自动收缩表空间并释放空间。需要注意的是，收缩表空间可能会导致数据库性能下降，因此应该谨慎使用。 ### 回答2： Oracle是一款常用的关系型数据库管理系统，它提供了删除表数据的方法，即delete语句。当使用delete语句删除表中的数据时，表空间并不会自动释放，需要做一些操作才能释放空间。 一般情况下，当使用delete语句删除表的数据时，数据库并不会自动收回这部分空间，而是将空间标记为可重用空间。这意味着，虽然数据被删除了，但是表所占用的空间并没有减少，它仍然会占用磁盘的存储空间，导致无法充分利用磁盘资源。 为了释放表所占用的空间，可以使用Oracle提供的命令vacuum。vacuum命令会为表的空间重新排序，合并相邻的块，从而释放表所占用的空间。vacuum命令的语法如下： vacuum FULL table_name; 其中，table_name是要释放空间的表名。vacuum命令需要在关闭数据库的情况下才能执行，在执行该命令前需要提前备份数据。 除了使用vacuum命令，还可以使用Oracle提供的其他方法来释放表空间，例如重新创建表、重建索引等。这些方法都需要谨慎使用，因为它们可能会影响到数据库的性能和数据的完整性。 总之，当使用delete语句删除表数据时，表空间并不会自动释放，需要使用vacuum命令或其他方法来释放空间。在释放空间的过程中，需要注意对数据库的性能和数据的完整性进行保护。 ### 回答3： 当使用Oracle数据库中的DELETE语句删除表中的行时，表空间中删除的行所占用的空间并不会被自动释放。这是因为Oracle的存储模型是一种延迟分配的方式，即仅当需要的时候才会分配足够的存储空间。 因此，一旦使用DELETE语句从表中删除行后，可以通过以下几种方式释放表空间： 1. 使用TRUNCATE TABLE语句 TRUNCATE TABLE语句用于清空表中的所有行。这个命令会立即释放表空间。但需要注意的是，使用TRUNCATE TABLE操作后，表将重置，且无法使用ROLLBACK进行回滚操作。 2. 使用ALTER TABLE语句 ALTER TABLE语句用于修改表的结构。可以先将表重命名为另外一个名称，然后再重新创建一个与原表结构相同的表，将数据插入到新表中，这样就会释放旧表所占用的表空间。 3. 使用OPTIMIZE TABLE语句 OPTIMIZE TABLE语句也可以释放表空间。该语句会重建表并优化存储空间。这个命令会自动释放表空间，并且还可以提高查询速度和优化性能。 4. 使用数据泵工具 可以使用Oracle提供的数据泵工具（Data Pump）导出和导入表，这也是一种释放表空间的方法。使用此方法将表空间中所有的数据导出，然后导入到一个新的表空间中，旧表空间的表空间空间将被释放。 总的来说，针对表空间释放的问题，可以根据自己的需要选择合适的方法。也需要注意，无论采用哪种方法释放表空间，在执行前一定要备份数据库，以避免不必要的损失。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值