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>

ITPub博客

Ora-01555错误的模拟及处理

最新文章