Database Reference
In-Depth Information
Let's see this concept in action with a small demonstration. In a small test database, I set up a table:
EODA@ORA12CR1> create table t as select * from all_objects;
Table created.
EODA@ORA12CR1> create index t_idx on t(object_name);
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.
I then created a very small undo tablespace and altered the system to use it. Note that by setting
AUTOEXTEND
off,
I have limited the size of all
UNDO
to be 10MB or less in this system:
EODA@ORA12CR1> create undo tablespace undo_small
2 datafile '/u01/dbfile/ORA12CR1/undo_small.dbf'
3 size 10m reuse
4 autoextend off
5 /
Tablespace created.
EODA@ORA12CR1> alter system set undo_tablespace = undo_small;
System altered.
Now, with only the small undo tablespace in use, I ran this block of code to do the
UPDATE
:
EODA@ORA12CR1> begin
2 for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
3 from t
4 where object_name > ' ' )
5 loop
6 update t
7 set object_name = lower(x.object_name)
8 where rowid = x.rid;
9 if ( mod(x.r,100) = 0 ) then
10 commit;
11 end if;
12 end loop;
13 commit;
14 end;
15 /
begin
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-06512: at line 2
