如何Shrink Undo表空间, 释放过度占用的空间
来源:互联网 发布:ubuntu 源码安装mysql 编辑:程序博客网 时间:2024/05/10 19:15
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.
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
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.
6.删除原UNDO表空间
11:34:00 SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
7.检查空间情况
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB Mirror_used_MB
21625 21625
现在我们可以采用如下步骤回收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案例一则
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
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.
6.删除原UNDO表空间
11:34:00 SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
7.检查空间情况
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB Mirror_used_MB
21625 21625
ASMCMD> exit
本文来自互联网
- 如何Shrink Undo表空间,释放过度占用的空间
- 如何Shrink Undo表空间、释放过度占用的空间
- 如何Shrink Undo表空间, 释放过度占用的空间
- 如何Shrink Undo表空间,释放过度占用的空间
- 如何Shrink Undo表空间,释放过度占用的空间
- 如何释放过度占用的Shrink Undo表空间
- [转]Oracle HowTo:如何Shrink Undo表空间,释放过度占用的空间
- oracle 释放过度使用的Undo表空间
- oracle 释放过度使用的Undo表空间
- Oracle 释放过度使用的Undo表空间
- Oracle 释放过度使用的Undo表空间
- Oracle 释放过度使用的Undo表空间
- 关于Oracle 释放过度使用的Undo表空间
- Oracle 释放过度使用的Undo表空间
- Oracle 释放过度使用的Undo表空间
- Oracle 释放过度使用的Undo表空间
- 释放UNDO表空间
- ORACLE释放UNDO表空间
- Struts1和Struts2的区别和对比
- [CentOS]another app is currently holding the yum lock;waiting for it to exit...解决
- linux下C++ 插件(plugin)实现技术
- 高效程序员的45个习惯:敏捷开发修炼之道(三)
- HDU-1698(线段树_区间更新_java手写)
- 如何Shrink Undo表空间, 释放过度占用的空间
- 构建自己的C/C++插件开发框架(一)——初步设想
- 构建自己的C/C++插件开发框架(二)——总体功能
- flex上传
- C++输出一个菱形
- 构建自己的C/C++插件开发框架(三)——总体结构
- 构建自己的C/C++插件开发框架(四)——核心层设计和实现
- 51单片机中断系统
- linux编程之文件与目录