如何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提供的信工具来察看空间占用情况.
[oracle@danaly ~]$ export oracle_sid=+asm
[oracle@danaly ~]$ asmcmd
asmcmd> du
used_mb      mirror_used_mb
  21625               21625
asmcmd> exit

空间已经释放。

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐