AI 助理
备案控制台
开发者社区 数据库 文章 正文

undo 表空间

2015-08-14 643
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 问题描述： 第一：磁盘空间共有450g,数据表空间、索引所占空间为350g,临时表空间60g，undo自增长到35g,包括其他空间以及安装所用空间,磁盘空间还剩3g左右。
问题描述：

第一：磁盘空间共有450g,数据表空间、索引所占空间为350g,临时表空间60g，undo自增长到35g,包括其他空间以及安装所用空间,磁盘空间还剩3g左右。

第二：现在需要为其添加主键并要为其赋值

第一次执行失败（使用序列插入主键值），查看的序列的最后值为560000000左右，估计是这段时间导致undo自增长到35g；

考虑到可能会出现报错，我就按照分区去插入主键

现在执行了2亿数据，undo表空间已经使用了24g，数据总量在6亿左右,考虑到这种情况，可能会再报“undo表空间不足”

undo生成

对数据进行修改时，数据库会变成undo信息，以便回到更改前的状态

数据修改前的会存储到undo段中

commit;rollback;之后按照在某个时间点释放undo空间

SQL> show parameter undo;

NAME                                 TYPE                                 VALUE
------------------------------------ ----------------------  
undo_management                string                AUTO 
undo_retention                       integer             900 
undo_tablespace                    string                UNDOTBS1
 
测试数据库为900s也就是15分钟释放空间

之前测试 insert < update < delete 产生的undo的总体情况

首先了解一下undo表空间如何使用


free(use)--->active--->unexpired--->expired(use)                                                 



update

update a
set a.x=a.x||'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';


查看前后的表空间大小

SELECT * FROM USER_SEGMENTS T;

A之前空间为14680064bytes,update后空间为100663296bytes,增加了大约80M左右。

查看前后undo空间大小

2015/8/13 7:35:49 0.85736083984375
2015/8/13 7:35:44 0.85736083984375
2015/8/13 7:35:39 0.85736083984375
2015/8/13 7:35:34 0.85736083984375
2015/8/13 7:35:29 0.85736083984375
2015/8/13 7:35:24 0.85736083984375
2015/8/13 7:35:19 0.85736083984375
2015/8/13 7:35:14 0.85736083984375
2015/8/13 7:35:09 0.85736083984375
2015/8/13 7:35:04 0.85736083984375
2015/8/13 7:34:59 0.85736083984375
2015/8/13 7:34:54 0.85736083984375
2015/8/13 7:34:49 0.85736083984375
2015/8/13 7:34:44 0.85736083984375
2015/8/13 7:34:39 0.85736083984375
2015/8/13 7:34:34 0.85736083984375
2015/8/13 7:34:29 0.85736083984375
2015/8/13 7:34:24 0.85736083984375
2015/8/13 7:34:19 0.85736083984375
2015/8/13 7:34:14 0.85736083984375
2015/8/13 7:34:09 0.85736083984375
2015/8/13 7:34:04 0.85736083984375
2015/8/13 7:33:59 0.85736083984375
2015/8/13 7:33:54 0.85736083984375
2015/8/13 7:33:49 0.85736083984375
2015/8/13 7:33:44 0.85736083984375
2015/8/13 7:33:39 0.85736083984375
2015/8/13 7:33:34 0.85736083984375
2015/8/13 7:33:29 0.85736083984375
2015/8/13 7:33:24 0.85736083984375
2015/8/13 7:33:19 0.85736083984375
2015/8/13 7:33:13 0.85736083984375
2015/8/13 7:33:08 0.85736083984375
2015/8/13 7:33:03 0.85736083984375
2015/8/13 7:32:58 0.85736083984375
2015/8/13 7:32:53 0.85736083984375
2015/8/13 7:32:48 0.85736083984375
2015/8/13 7:32:45 0.84954833984375
2015/8/13 7:32:38 0.83392333984375
2015/8/13 7:32:33 0.82611083984375
2015/8/13 7:32:31 0.81829833984375
2015/8/13 7:32:23 0.88275146484375
2015/8/13 7:32:18 0.88861083984375
2015/8/13 7:32:13 0.88665771484375
2015/8/13 7:32:08 0.88616943359375
2015/8/13 7:32:04 0.87945556640625
2015/8/13 7:31:58 0.86480712890625
2015/8/13 7:31:53 0.85699462890625
2015/8/13 7:31:50 0.84918212890625
2015/8/13 7:31:43 0.83355712890625
2015/8/13 7:31:38 0.81793212890625
2015/8/13 7:31:33 0.79840087890625
2015/8/13 7:31:29 0.78179931640625
2015/8/13 7:31:23 0.74957275390625
2015/8/13 7:31:18 0.74176025390625
2015/8/13 7:31:13 0.74176025390625
2015/8/13 7:31:08 0.74176025390625
2015/8/13 7:31:03 0.74176025390625
2015/8/13 7:30:58 0.74176025390625
2015/8/13 7:30:53 0.74176025390625
2015/8/13 7:30:48 0.74176025390625
2015/8/13 7:30:43 0.74176025390625
2015/8/13 7:30:38 0.74176025390625
2015/8/13 7:30:33 0.74176025390625
2015/8/13 7:30:28 0.74176025390625
2015/8/13 7:30:23 0.74176025390625
2015/8/13 7:30:18 0.74176025390625
2015/8/13 7:30:13 0.74176025390625
2015/8/13 7:30:08 0.74176025390625
2015/8/13 7:30:03 0.74176025390625
2015/8/13 7:29:58 0.74176025390625
2015/8/13 7:29:53 0.74176025390625
2015/8/13 7:29:48 0.74176025390625
2015/8/13 7:29:43 0.74176025390625
2015/8/13 7:29:38 0.74176025390625
2015/8/13 7:29:33 0.74176025390625
2015/8/13 7:29:28 0.74176025390625
2015/8/13 7:29:23 0.74176025390625
2015/8/13 7:29:18 0.74176025390625
2015/8/13 7:29:13 0.74176025390625
2015/8/13 7:29:08 0.74176025390625
2015/8/13 7:29:03 0.74176025390625
2015/8/13 7:28:58 0.74176025390625
2015/8/13 7:28:53 0.74176025390625
2015/8/13 7:28:48 0.74176025390625
2015/8/13 7:28:43 0.74176025390625
2015/8/13 7:28:38 0.74176025390625
2015/8/13 7:28:33 0.74176025390625
2015/8/13 7:28:28 0.74176025390625
2015/8/13 7:28:23 0.74176025390625
2015/8/13 7:28:18 0.74176025390625
2015/8/13 7:28:13 0.74176025390625
2015/8/13 7:28:08 0.74176025390625
2015/8/13 7:28:03 0.74176025390625
2015/8/13 7:27:58 0.74176025390625
2015/8/13 7:27:53 0.74176025390625
2015/8/13 7:27:48 0.74176025390625
2015/8/13 7:27:43 0.74176025390625
2015/8/13 7:27:38 0.74176025390625
2015/8/13 7:27:33 0.74176025390625
2015/8/13 7:27:28 0.74176025390625
2015/8/13 7:27:23 0.74176025390625
2015/8/13 7:27:18 0.74176025390625
2015/8/13 7:27:13 0.74176025390625
2015/8/13 7:27:08 0.74176025390625
2015/8/13 7:27:03 0.74176025390625
2015/8/13 7:26:58 0.74176025390625
2015/8/13 7:26:53 0.74176025390625
2015/8/13 7:26:48 0.74176025390625
2015/8/13 7:26:43 0.74176025390625
2015/8/13 7:26:38 0.74176025390625
2015/8/13 7:26:33 0.74176025390625
2015/8/13 7:26:28 0.74176025390625
2015/8/13 7:26:23 0.74176025390625
2015/8/13 7:26:18 0.74176025390625
2015/8/13 7:26:13 0.74176025390625
2015/8/13 7:26:08 0.74176025390625
2015/8/13 7:26:03 0.74176025390625
2015/8/13 7:25:58 0.74176025390625
2015/8/13 7:25:53 0.74176025390625
2015/8/13 7:25:48 0.74176025390625
2015/8/13 7:25:43 0.74176025390625
2015/8/13 7:25:38 0.74176025390625
2015/8/13 7:25:33 0.74176025390625
2015/8/13 7:25:28 0.74176025390625
2015/8/13 7:25:23 0.74176025390625
2015/8/13 7:25:18 0.74176025390625
2015/8/13 7:25:13 0.74176025390625
2015/8/13 7:25:08 0.74176025390625
2015/8/13 7:25:03 0.74176025390625
2015/8/13 7:24:58 0.74176025390625
2015/8/13 7:24:53 0.74176025390625
2015/8/13 7:24:48 0.74176025390625
2015/8/13 7:24:43 0.74176025390625
2015/8/13 7:24:38 0.74176025390625
2015/8/13 7:24:33 0.74176025390625
2015/8/13 7:24:28 0.74176025390625
2015/8/13 7:24:23 0.74176025390625
2015/8/13 7:24:18 0.74176025390625
2015/8/13 7:24:13 0.74176025390625
2015/8/13 7:24:08 0.74176025390625
2015/8/13 7:24:03 0.74176025390625
2015/8/13 7:23:58 0.74176025390625
2015/8/13 7:23:53 0.74176025390625
2015/8/13 7:23:48 0.74176025390625
2015/8/13 7:23:43 0.74176025390625
2015/8/13 7:23:38 0.74176025390625
2015/8/13 7:23:33 0.74176025390625
2015/8/13 7:23:28 0.74176025390625
2015/8/13 7:23:23 0.74176025390625
2015/8/13 7:23:18 0.74176025390625
2015/8/13 7:23:13 0.74176025390625
2015/8/13 7:23:08 0.74176025390625
2015/8/13 7:23:03 0.74176025390625
2015/8/13 7:22:58 0.74176025390625
2015/8/13 7:22:53 0.74176025390625
2015/8/13 7:22:48 0.74176025390625
2015/8/13 7:22:43 0.74176025390625
2015/8/13 7:22:38 0.74176025390625
2015/8/13 7:22:33 0.74176025390625
2015/8/13 7:22:28 0.74176025390625
2015/8/13 7:22:23 0.74176025390625
2015/8/13 7:22:18 0.74176025390625


占用undo空间大约为100M左右

事务未提交时，undo段为ACTIVE;

select  t.* from dba_undo_extents t where t.STATUS='ACTIVE' order by t.STATUS;

粘贴部分值段
_SYSSMU6$ 0 65536 ACTIVE 8
_SYSSMU6$ 1 65536 ACTIVE 8
_SYSSMU6$ 2 1048576 ACTIVE 128
_SYSSMU6$ 3 1048576 ACTIVE 128
_SYSSMU6$ 4 1048576 ACTIVE 128
_SYSSMU6$ 5 1048576 ACTIVE 128
_SYSSMU6$ 6 1048576 ACTIVE 128
_SYSSMU6$ 7 1048576 ACTIVE 128
_SYSSMU6$ 8 1048576 ACTIVE 128
_SYSSMU6$ 9 1048576 ACTIVE 128
_SYSSMU6$ 10 1048576 ACTIVE 128
_SYSSMU6$ 11 1048576 ACTIVE 128
_SYSSMU6$ 12 1048576 ACTIVE 128
_SYSSMU6$ 13 1048576 ACTIVE 128
_SYSSMU6$ 14 1048576 ACTIVE 128
_SYSSMU6$ 15 1048576 ACTIVE 128
_SYSSMU6$ 16 1048576 ACTIVE 128
_SYSSMU6$ 17 1048576 ACTIVE 128
_SYSSMU6$ 18 1048576 ACTIVE 128
_SYSSMU6$ 19 1048576 ACTIVE 128
_SYSSMU6$ 20 1048576 ACTIVE 128
_SYSSMU6$ 21 1048576 ACTIVE 128
_SYSSMU6$ 22 1048576 ACTIVE 128
_SYSSMU6$ 23 1048576 ACTIVE 128
_SYSSMU6$ 24 1048576 ACTIVE 128
_SYSSMU6$ 25 1048576 ACTIVE 128
_SYSSMU6$ 26 1048576 ACTIVE 128
_SYSSMU6$ 27 1048576 ACTIVE 128
_SYSSMU6$ 28 1048576 ACTIVE 128
_SYSSMU6$ 29 1048576 ACTIVE 128
_SYSSMU6$ 30 1048576 ACTIVE 128
_SYSSMU6$ 31 1048576 ACTIVE 128
_SYSSMU6$ 32 1048576 ACTIVE 128
_SYSSMU6$ 33 1048576 ACTIVE 128
_SYSSMU6$ 34 1048576 ACTIVE 128
_SYSSMU6$ 35 1048576 ACTIVE 128
_SYSSMU6$ 36 1048576 ACTIVE 128
_SYSSMU6$ 37 1048576 ACTIVE 128
_SYSSMU6$ 38 1048576 ACTIVE 128
_SYSSMU6$ 39 1048576 ACTIVE 128
_SYSSMU6$ 40 1048576 ACTIVE 128
_SYSSMU6$ 41 1048576 ACTIVE 128
_SYSSMU6$ 42 1048576 ACTIVE 128
_SYSSMU6$ 43 1048576 ACTIVE 128
_SYSSMU6$ 44 1048576 ACTIVE 128
_SYSSMU6$ 45 1048576 ACTIVE 128
_SYSSMU6$ 46 1048576 ACTIVE 128
_SYSSMU6$ 47 1048576 ACTIVE 128
_SYSSMU6$ 48 1048576 ACTIVE 128
_SYSSMU6$ 49 1048576 ACTIVE 128
_SYSSMU6$ 50 1048576 ACTIVE 128
_SYSSMU6$ 51 1048576 ACTIVE 128
_SYSSMU6$ 52 1048576 ACTIVE 128
_SYSSMU6$ 53 1048576 ACTIVE 128
_SYSSMU6$ 54 1048576 ACTIVE 128
_SYSSMU6$ 55 1048576 ACTIVE 128
_SYSSMU6$ 56 1048576 ACTIVE 128
_SYSSMU6$ 57 1048576 ACTIVE 128
_SYSSMU6$ 58 1048576 ACTIVE 128
_SYSSMU6$ 59 1048576 ACTIVE 128
_SYSSMU6$ 60 1048576 ACTIVE 128
_SYSSMU6$ 61 1048576 ACTIVE 128
_SYSSMU6$ 62 1048576 ACTIVE 128
_SYSSMU6$ 63 1048576 ACTIVE 128
_SYSSMU6$ 64 8388608 ACTIVE 1024
_SYSSMU6$ 65 8388608 ACTIVE 1024
_SYSSMU6$ 66 8388608 ACTIVE 1024
_SYSSMU6$ 67 8388608 ACTIVE 1024
_SYSSMU6$ 68 8388608 ACTIVE 1024
_SYSSMU6$ 69 8388608 ACTIVE 1024
_SYSSMU6$ 70 8388608 ACTIVE 1024
_SYSSMU6$ 71 8388608 ACTIVE 1024
_SYSSMU6$ 72 8388608 ACTIVE 1024
_SYSSMU6$ 73 7340032 ACTIVE 896
_SYSSMU6$ 74 5242880 ACTIVE 640
_SYSSMU6$ 75 65536 ACTIVE 8
_SYSSMU6$ 76 65536 ACTIVE 8
_SYSSMU6$ 77 65536 ACTIVE 8
_SYSSMU6$ 78 65536 ACTIVE 8
_SYSSMU6$ 79 65536 ACTIVE 8
_SYSSMU6$ 80 65536 ACTIVE 8
_SYSSMU6$ 81 65536 ACTIVE 8
_SYSSMU6$ 82 65536 ACTIVE 8
_SYSSMU6$ 83 65536 ACTIVE 8
_SYSSMU6$ 84 65536 ACTIVE 8
_SYSSMU6$ 85 65536 ACTIVE 8
_SYSSMU6$ 86 65536 ACTIVE 8
_SYSSMU6$ 87 65536 ACTIVE 8
_SYSSMU6$ 88 65536 ACTIVE 8
_SYSSMU6$ 89 65536 ACTIVE 8
_SYSSMU6$ 90 65536 ACTIVE 8
_SYSSMU6$ 91 65536 ACTIVE 8
_SYSSMU6$ 92 65536 ACTIVE 8
_SYSSMU6$ 93 65536 ACTIVE 8
_SYSSMU6$ 94 65536 ACTIVE 8
_SYSSMU6$ 95 65536 ACTIVE 8
_SYSSMU6$ 96 65536 ACTIVE 8
_SYSSMU6$ 97 65536 ACTIVE 8
_SYSSMU6$ 98 65536 ACTIVE 8
_SYSSMU6$ 99 65536 ACTIVE 8
_SYSSMU6$ 100 65536 ACTIVE 8
_SYSSMU6$ 101 65536 ACTIVE 8
_SYSSMU6$ 102 65536 ACTIVE 8
_SYSSMU6$ 103 65536 ACTIVE 8
_SYSSMU6$ 104 65536 ACTIVE 8
_SYSSMU6$ 105 65536 ACTIVE 8
_SYSSMU6$ 106 65536 ACTIVE 8
_SYSSMU6$ 107 65536 ACTIVE 8
_SYSSMU6$ 108 65536 ACTIVE 8
_SYSSMU6$ 109 65536 ACTIVE 8
_SYSSMU6$ 110 65536 ACTIVE 8
_SYSSMU6$ 111 65536 ACTIVE 8
_SYSSMU6$ 112 65536 ACTIVE 8
_SYSSMU6$ 113 65536 ACTIVE 8
_SYSSMU6$ 114 65536 ACTIVE 8
_SYSSMU6$ 115 65536 ACTIVE 8
_SYSSMU6$ 116 65536 ACTIVE 8
_SYSSMU6$ 117 65536 ACTIVE 8
_SYSSMU6$ 118 65536 ACTIVE 8
_SYSSMU6$ 119 65536 ACTIVE 8
_SYSSMU6$ 120 65536 ACTIVE 8
_SYSSMU6$ 121 65536 ACTIVE 8
_SYSSMU6$ 122 65536 ACTIVE 8
_SYSSMU6$ 123 65536 ACTIVE 8
_SYSSMU6$ 124 65536 ACTIVE 8
_SYSSMU6$ 125 65536 ACTIVE 8
_SYSSMU6$ 126 65536 ACTIVE 8
_SYSSMU6$ 127 65536 ACTIVE 8
_SYSSMU6$ 128 65536 ACTIVE 8
_SYSSMU6$ 129 65536 ACTIVE 8
_SYSSMU6$ 130 65536 ACTIVE 8
_SYSSMU6$ 131 65536 ACTIVE 8
_SYSSMU6$ 132 65536 ACTIVE 8
_SYSSMU6$ 133 65536 ACTIVE 8
_SYSSMU6$ 134 65536 ACTIVE 8
_SYSSMU6$ 135 65536 ACTIVE 8
_SYSSMU6$ 136 65536 ACTIVE 8
_SYSSMU6$ 137 65536 ACTIVE 8
_SYSSMU6$ 138 65536 ACTIVE 8
_SYSSMU6$ 139 65536 ACTIVE 8
_SYSSMU6$ 140 65536 ACTIVE 8
_SYSSMU6$ 141 65536 ACTIVE 8
_SYSSMU6$ 142 65536 ACTIVE 8
_SYSSMU6$ 143 65536 ACTIVE 8
_SYSSMU6$ 144 65536 ACTIVE 8
_SYSSMU6$ 145 6291456 ACTIVE 768
_SYSSMU6$ 146 2097152 ACTIVE 256
_SYSSMU6$ 147 1048576 ACTIVE 128
_SYSSMU6$ 148 1048576 ACTIVE 128
_SYSSMU6$ 149 65536 ACTIVE 8
_SYSSMU6$ 150 65536 ACTIVE 8
_SYSSMU6$ 151 65536 ACTIVE 8
_SYSSMU6$ 152 8388608 ACTIVE 1024
_SYSSMU6$ 153 2097152 ACTIVE 256
_SYSSMU6$ 154 1048576 ACTIVE 128
_SYSSMU6$ 155 8388608 ACTIVE 1024
_SYSSMU6$ 156 2097152 ACTIVE 256
_SYSSMU6$ 157 4194304 ACTIVE 512
_SYSSMU6$ 158 1048576 ACTIVE 128
_SYSSMU6$ 159 1048576 ACTIVE 128
_SYSSMU6$ 160 1048576 ACTIVE 128
_SYSSMU6$ 161 1048576 ACTIVE 128
_SYSSMU6$ 162 1048576 ACTIVE 128
_SYSSMU6$ 163 1048576 ACTIVE 128
_SYSSMU6$ 164 65536 ACTIVE 8
_SYSSMU6$ 165 65536 ACTIVE 8
_SYSSMU6$ 166 65536 ACTIVE 8
_SYSSMU6$ 167 8388608 ACTIVE 1024
_SYSSMU6$ 168 8388608 ACTIVE 1024
_SYSSMU6$ 169 8388608 ACTIVE 1024
_SYSSMU6$ 170 8388608 ACTIVE 1024
_SYSSMU6$ 171 8388608 ACTIVE 1024
_SYSSMU6$ 172 8388608 ACTIVE 1024
_SYSSMU6$ 173 1048576 ACTIVE 128
_SYSSMU6$ 174 1048576 ACTIVE 128



rollback,commit后undo段的状态变为unexpired，这样还是不能使用


SYS _SYSSMU6$ 0 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 1 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 2 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 3 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 4 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 5 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 6 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 7 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 8 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 9 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 10 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 11 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 12 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 13 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 14 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 15 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 16 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 17 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 18 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 19 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 20 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 21 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 22 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 23 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 24 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 25 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 26 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 27 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 28 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 29 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 30 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 31 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 32 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 33 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 34 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 35 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 36 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 37 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 38 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 39 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 40 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 41 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 42 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 43 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 44 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 45 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 46 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 47 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 48 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 49 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 50 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 51 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 52 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 53 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 54 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 55 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 56 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 57 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 58 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 59 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 60 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 61 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 62 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 63 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 64 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 65 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 66 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 67 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 68 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 69 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 70 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 71 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 72 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 73 7340032 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 74 5242880 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 75 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 76 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 77 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 78 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 79 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 80 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 81 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 82 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 83 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 84 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 85 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 86 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 87 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 88 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 89 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 90 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 91 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 92 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 93 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 94 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 95 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 96 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 97 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 98 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 99 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 100 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 101 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 102 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 103 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 104 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 105 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 106 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 107 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 108 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 109 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 110 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 111 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 112 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 113 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 114 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 115 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 116 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 117 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 118 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 119 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 120 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 121 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 122 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 123 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 124 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 125 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 126 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 127 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 128 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 129 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 130 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 131 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 132 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 133 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 134 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 135 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 136 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 137 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 138 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 139 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 140 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 141 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 142 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 143 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 144 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 145 6291456 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 146 2097152 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 147 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 148 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 149 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 150 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 151 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 152 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 153 2097152 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 154 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 155 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 156 2097152 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 157 4194304 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 158 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 159 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 160 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 161 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 162 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 163 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 164 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 165 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 166 65536 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 167 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 168 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 169 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 170 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 171 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 172 8388608 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 173 1048576 UNEXPIRED UNDOTBS1
SYS _SYSSMU6$ 174 1048576 UNEXPIRED UNDOTBS1


三个状态的含义
ACTIVE ：还有活动事务在使用 undo。这部分空间属于暂时不能使用的空间。
EXPIRED  ：考虑到 undo retention 之后，这些 undo 已经过期了。这部分空间是可以重用的。
UNEXPIRED  ：考虑 undo retention 之后，这些 undo 还没有过期，但是已经没有活动事务在使用了。在超过 undo retention 之后，这部分空间会变成 expired 状态，然后就可以重用了。


提交或者回滚后，undo段空间会变为UNEXPIRED,等到15分钟后，状态会变为expired，就可以重新使用了


undo释放

undo的释放就是状态的变化， 平时我们看到如下情况，update提交后，过一段时间undo段空间就会变小，那么就是说最后的状态还可能是释放了
最后的状态应该是expired-->free空间

alter system set undo_retention=60 scope=both; 

该命令即可生效

我们就应该设置较小的undo_retention，这样提交后，状态就会很快变为expired；

测试时发现确实释放时间可能长于60s。。。


后面是按照分区去做，undo空间使用在8g左右，较好的实现了执之前的想法。但是一次提交也是不可能的对于我们这种数据量和undo表空间。。

对数据库设计需要慎重。。。
文章标签：
数据库
樱桃味
目录
相关文章
度假的小鱼
|
Oracle 关系型数据库 数据库
9-4 Oracle管理表空间和数据文件
9-4 Oracle管理表空间和数据文件
度假的小鱼
118 0
leshami
|
SQL 监控 Oracle
关于undo表空间配置错误的ORA-30012
      undo表空间是Oracle体系结构的重要组成部分，为什么我们可以回滚，就是因为有它。数据库任意数据的修改都会在undo表空间里生成前镜像，一是可以回滚，二是可以实现并发，以及一致性查询。
leshami
1167 0
小小工匠
|
SQL 存储 监控
Oracle-UNDO表空间解读
Oracle-UNDO表空间解读
小小工匠
632 0
IT邦德
|
Oracle 关系型数据库
Oracle管理表空间和数据文件
Oracle管理表空间和数据文件
IT邦德
200 0
余二五
|
关系型数据库 Oracle
表空间大小到底跟什么有关
余二五
1032 2
lfreeali
|
Oracle 关系型数据库 SQL
[20180423]表空间闪回与snapshot standby
[20180423]flashback tablespace与snapshot standby.txt --//缺省建立表空间是打开flashback on,如果某个表空间flashback off,在dg启动snapshot standby时注意,可能"回不来", --//通过测试说明问题.
lfreeali
1272 0
z奶油面包
查看所有表空间大小
版权声明：转载请注明出处:http://blog.csdn.net/dajitui2024 https://blog.csdn.net/dajitui2024/article/details/79396637 1.
z奶油面包
1085 0
技术小阿哥
|
关系型数据库 Oracle
Undo表空间
技术小阿哥
914 0
余二五
|
Oracle 关系型数据库 数据库
UNDO 表空间管理
余二五
1065 0
科技小能手
|
监控 SQL 关系型数据库
UNDO表空间监控说明
科技小能手
1097 0