grant alter rollback segment, select any table to system;
create or replace procedure system.use_rbs (rbsname in varchar2)
  -----------------------------------------------------------------
  --- PROCEDURE TO TURN ON A ROLLBACK SEGMENT, IF IT IS OFFLINE  --
  --- AND TO SET THE RBS FOR TRANSACTIONS                        --
  ---                                                            --
  --- BIJU THOMAS - 11/23/98                                     --
  -----------------------------------------------------------------
as

  wstatus  varchar2 (30);
  wcursor  integer;
  wretcode integer;
  wsql     varchar2 (255);

begin

  select status 
  into   wstatus
  from   dba_rollback_segs
  where  segment_name = upper(rbsname);

  if wstatus = 'OFFLINE' then

     wcursor  := dbms_sql.open_cursor;
     wsql     := 'alter rollback segment ' || rbsname || ' online';
     dbms_sql.parse (wcursor, wsql, dbms_sql.v7);
     wretcode := dbms_sql.execute (wcursor);
     dbms_sql.close_cursor (wcursor);

  end if;

  dbms_transaction.use_rollback_segment (rbsname);

exception

  when no_data_found then
       raise_application_error (-20102, 'Error - RBS ' || rbsname || ' does not exist.');

end;
/
create public synonym use_rbs for system.use_rbs;
grant  execute on system.use_rbs to public;