如何处理UNDO表空间太大的问题
来源:互联网 发布:chrome for linux 编辑:程序博客网 时间:2024/04/28 18:55
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.检查空间情况
- 如何处理UNDO表空间太大的问题
- 如何处理undo tablespace 表空间太大的问题
- undo太大的处理办法
- rac环境处理undo表空间过大的问题!
- UNDO表空间一直增长问题处理
- undo 表空间过大的处理方法
- UNDO表空间失败的处理方法
- 如何缩小undo表空间的大小
- 如何处理Oracle的UNDO表空间所对应的数据文件过大
- 如何Shrink Undo表空间,释放过度占用的空间
- 如何Shrink Undo表空间、释放过度占用的空间
- 如何Shrink Undo表空间, 释放过度占用的空间
- 如何Shrink Undo表空间,释放过度占用的空间
- 如何Shrink Undo表空间,释放过度占用的空间
- undo表空间异常增大印发的空间不足问题
- 回滚表空间处理 --undo表空间undotbs
- UNDO表空间与Undo的认识
- undo 表空间满了的处理方法
- Ubuntu 12.04 安装TFTP server
- 2011/5、4
- String创建原理
- 阿里云实习生面试
- 分区文件http://wenku.baidu.com/view/d839d1868762caaedd33d4b7.html
- 如何处理UNDO表空间太大的问题
- 存储过程的复制测试
- 纪念本拉登____基地组织首脑,衷祷20世纪世界上唯一一支公开抗击美国霸权主义的强有力团体
- wince系统PDA 条码扫描 设计
- (转)WSAIoctl Function
- 26. android Notification 状态栏通知
- 没有3G的日子
- 信息系统项目管理师-整体管理-思维导图
- Java语法总结 - 内部类