set transaction use rollback segment

 
  2
SQL> create table t
  2  as
  3  select * from all_objects;

Table created.

SQL>
SQL>
SQL> set echo on
SQL> drop table done;

Table dropped.

SQL> create table done( object_id int );

Table created.

SQL> insert into done values ( 0 );

1 row created.

SQL>
SQL> declare
  2          l_cnt number;
  3          l_max number;
  4  begin
  5          select object_id into l_cnt from done;
  6          select max(object_id) into l_max from t;
  7
  8          while ( l_cnt < l_max )
  9          loop
 10                  update t
 11                     set object_name = lower(object_name)
 12                   where object_id > l_cnt
 13                     and object_id <= l_cnt+100;
 14
 15                  update done set object_id = object_id+100;
 16
 17                  commit;
 18                  set transaction use rollback segment rbs_small;
 19                  l_cnt := l_cnt + 100;
 20          end loop;
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table t;

Table dropped.

SQL>
SQL> --

Related examples in the same category

1.	Commit an insert statement in PL SQL
2.	The pragma is legal in top-level anonymous blocks:
3.	The pragma is not legal in nested blocks:
4.	The pragma is valid in both standalone and local subprograms.
5.	The pragma is valid in a packaged procedure.
6.	interaction between savepoints and autonomous transactions.
7.	Calling an autonomous function from SQL.
8.	Autonomous transactions.

set transaction use rollback segment : Transaction « PL SQL « Oracle PL / SQL

Related examples in the same category