使用undo_retention参数与UNDO表空间GUARANTEE功能避免ORA-01555错误

﹏ヽ暗。殇╰゛Y 2022-04-04 06:35 249阅读 0赞

ORA-01555便是著名的snapshot too old（快照太旧）问题，在Oracle早期版本中这个报错一度成为Oracle最为棘手的问题。每一名开发人员或者DBA都不愿意看到这样的报错。  
 导致ORA-01555错误的最常见原因是当一个查询需要使用已被覆盖的回滚段中的前映像构造一致性读时。那么我们有没有办法可以保证UNDO表空间在我们制定的时间内保留的数据不被覆写呢？答案是肯定的，随着Oracle版本的升级和功能完善，在Oracle 10g版本中UNDO表空间提供了GUARANTEE功能。此功能便能保证我们的UNDO数据不会被轻易的覆写。  
  
  
1.创建UNDO表空间UNDOTBS\_GUARANTEE  
注意，我们这里未设置其为自动扩展。  
sql> create undo tablespace UNDOTBS\_GUARANTEE datafile '/oracle/ora10gR2/oradata/ora10g/undotbs\_guarantee.dbf' size 5m;  
//当然，11g下面是默认有undotbs01.dbf，即默认有undotbs空间的。一般是自动扩容的。  
  
Tablespace created.  
  
  
2.启用UNDO表空间的GUARANTEE功能  
sql> alter tablespace UNDOTBS\_GUARANTEE retention guarantee;  
  
  
Tablespace altered.  
  
  
3.调整与UNDO表空间相关的参数  
1）查询当前数据库中有关UNDO相关的信息  
sql> show parameter undo  
  
  
NAME               TYPE                 VALUE  
\------------------ -------------------- -------------  
undo\_management    string               AUTO  
undo\_retention     integer              900  
undo\_tablespace    string               UNDOTBS1  
  
  
此时系统默认的UNDO表空间是“UNDOTBS1”，我们调整到新创建的UNDOTBS\_GUARANTEE表空间上；  
UNDO默认的保留时间是15分钟（900秒），我们调整为1.5小时（5400秒）。  
sql> alter system set undo\_retention=5400;  
  
  
System altered.  
  
  
sql> alter system set undo\_tablespace=UNDOTBS\_GUARANTEE;  
  
  
System altered.  
  
  
2）调整后的UNDO相关参数  
sql> show parameter undo  
  
  
NAME               TYPE                 VALUE  
\------------------ -------------------- ------------------  
undo\_management    string               AUTO  
undo\_retention     integer              5400  
undo\_tablespace    string               UNDOTBS\_GUARANTEE  
  
  
4.测试当UNDO表空间在GUARANTEE条件下的使用效果  
sql> update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000;  
  
  
19999 rows updated.

sql> update t set object\_name = 'secjssecalskjdf;alksjdfaslkdjf;alskdjfa;sldooler' where rownum<20000;

update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000  
       \*  
ERROR at line 1:  
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS\_GUARANTEE'  
  
  
在报错之后我们这里提交事务。  
  
sql> commit;  
  
  
Commit complete.  
  
  
sql> update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000;  
update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000  
       \*  
ERROR at line 1:  
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS\_GUARANTEE'  
  
  
由于UNDO表空间在guarantee状态下，是不允许被覆写的，因为没有多余的空间保留更新前的数据。  
因此，在UNDO表空间非自动扩展并且UNDO表空间是GUARANTEE状态下，在保留时限内UNDO表空间中保留的数据不允许被覆写！  
  
  
5.测试当UNDO表空间在NOGUARANTEE条件下的使用效果  
1）调整UNDO表空间为nOGUARANTEE  
sec@ora10g> alter tablespace UNDOTBS\_GUARANTEE retention noguarantee;  
  
  
Tablespace altered.  
  
  
2）再次测试  
sec@ora10g> update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000;  
  
  
19999 rows updated.  
  
  
sec@ora10g> update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000;  
update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000  
\*  
ERROR at line 1:  
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS\_GUARANTEE'  
  
  
提交事务。  
sec@ora10g> commit;  
  
  
Commit complete.  
  
  
sec@ora10g> update t set object\_name = 'asajsdfjalskjdf;alksjdfaslkdjf;alskdjfa;sldkjfad' where rownum<20000;  
  
  
19999 rows updated.  
  
  
可见在事务提交之后，再次执行更新语句后成功。因此可以判断此时部分UNDO表空间的内容已被覆写！  
  
  
6.小结  
  这便是UNDO表空间GUARANTEE与NOGUARANTEE之间的区别。  
  因此在GUARANTEE状态下，可以最有效的保证UNDO表空间中在UNDO\_RETENTION规定期间内保留的足够多的数据，前提是我们需要为UNDO表空间分配足够大的空间，防止因UNDO表空间剩余空间过小导致SQL语句报错。