Ora-01555錯誤的模擬及處理

1、建立一個更小的UNDO表空間：

SQL> create undo tablespace smallundotbs
  2  datafile '/u01/oradata/denver/smallundo01.dbf'
  3  size 1m ;

Tablespace created.

SQL>

 

2、設定undo_retention   =  5

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                               integer     5
undo_suppress_errors                 boolean     FALSE
undo_tablespace                           string      UNDOTBS1
SQL> alter system set undo_retention = 1;

System altered.

SQL>

 

3、SQL> select addr,used_ublk from v$transaction;

no rows selected

SQL>

沒有transaction佔用undo段，可以放心的更改undo表空間

 

4、SQL> alter system set undo_tablespace=smallundotbs;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      SMALLUNDOTBS
SQL>

 

5、SQL>  create table t
2 as
3 select *
4 from all_objects
5 order by dbms_random.random;
Table created.
SQL>  alter table t add constraint t_pk primary key(object_id)
2 /
Table altered.
SQL> exec dbms_stats.gather_table_stats( user, 'T', cascade=> true );
PL/SQL procedure successfully completed.

 

6、在當前session裡更新表：

SQL> begin
2 for x in ( select rowid rid from t )
3 loop
4 update t set object_name = lower(object_name) where rowid = x.rid;
5 commit;
6 end loop;
7 end;

/

 

7、在另一個session裡查詢表：

SQL> declare
cursor c is
select /*+ first_rows */ object_name
from t
order by object_id;
l_object_name t.object_name%type;
l_rowcnt number := 0;
begin
open c;
loop
fetch c into l_object_name;
exit when c%notfound;
dbms_lock.sleep( 0.01 );
l_rowcnt := l_rowcnt+1;
end loop;
close c;
exception
when others then
dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
raise;
end;

/

declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 13 with name "_SYSSMU13$" too small
ORA-06512: at line 20

SQL>

出現錯誤了

 

解決辦法：擴充套件表空間大小

SQL> select file_name from dba_data_files where tablespace_name='SMALLUNDOTBS';

FILE_NAME
----------------------------------------
/u01/oradata/denver/smallundo01.dbf

SQL>

SQL> alter database datafile '&F' autoextend on next 1m maxsize 1024m;
Enter value for f: /u01/oradata/smallundotbs1.dbf
old   1: alter database datafile '&F' autoextend on next 1m maxsize 1024m
new   1: alter database datafile '/u01/oradata/smallundotbs1.dbf' autoextend on next 1m maxsize 1024m
但是再次執行還是報錯

仔細的查了查想到了undo_retention                       integer     1
還沒改過來，改retention大小：

SQL> alter system set undo_retention=10080 scope=both;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10080
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      SMALLUNDOTBS
SQL>

 

8、再次執行程式碼

SQL> begin
  2  for x in ( select rowid rid from t )
  3  loop
  4  update t set object_name = lower(object_name) where rowid = x.rid;
  5  commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> declare
  2  cursor c is
  3  select /*+ first_rows */ object_name
  4  from t
  5  order by object_id;
  6  l_object_name t.object_name%type;
  7  l_rowcnt number := 0;
  8  begin
  9  open c;
 10  loop
 11  fetch c into l_object_name;
 12  exit when c%notfound;
 13  dbms_lock.sleep( 0.01 );
 14  l_rowcnt := l_rowcnt+1;
 15  end loop;
 16  close c;
 17  exception
 18  when others then
 19  dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
 20  raise;
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL>