ITPub博客

首页 > 数据库 > Oracle > undo 表空间

undo 表空间

原创 Oracle 作者:gh_95533 时间:2015-08-14 16:27:02 0 删除 编辑
问题描述:

第一:磁盘空间共有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表空间。。

对数据库设计需要慎重。。。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29162273/viewspace-1771660/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-10-24

  • 博文量
    44
  • 访问量
    82861