Procedure to bring all rollback segments online

Home

Toolkit:

• Index
• Scripts
• Quick Tips
• Articles / Publications
• Links

My OraFAQ Blog

Contact me

Schedule this procedure as a job to make sure all your rollback segments are online -- useful if you have a backup job that takes some RBS offline, eg. to make sure a large consistent dump uses the bulk segment

create or replace procedure rollbacks_online as

--Procedure to make sure specific rollback segments are online. 
--Assumes rollback segment names
--RBS01, RBS02, RBS03, RBS04, RBS05, RBS06, RBS07, RBS08, RBS09
--Modify to suit your own RBS names
 
	this_status varchar2(20);
	i number;
	error_count number := 0;
	err_messages varchar2(4000);
  
  function return_rbs_status(rbs_name in varchar2) return varchar2
  is

    status varchar2(20);
    stmt varchar2(2000);
    chandle binary_integer;
    results integer;

  
  begin
    stmt := 'select status from dba_rollback_segs where segment_name = ''' 
            || rbs_name || '''' ;
    chandle := dbms_sql.open_cursor ;
    dbms_sql.parse ( chandle, stmt, DBMS_SQL.NATIVE ) ;
    dbms_sql.define_column (chandle, 1, status, 16);
  	results := dbms_sql.execute_and_fetch ( chandle, true ) ;
    dbms_sql.column_value (chandle, 1, status);
    dbms_sql.close_cursor ( chandle ) ;
    commit;
    return status;
  end return_rbs_status;

  
begin
  
  i := 1;
  
  while i < 10 loop
  
    begin <>
    
      this_status := return_rbs_status ('RBS0' || i);
      if this_status = 'OFFLINE' then
      	execute immediate ('alter rollback segment rbs0' || i || ' online');
      end if;
    	
    exception  
    	when others then
      	error_count := error_count + 1;
    		err_messages := err_messages || '::::' || sqlerrm;
    end ilb;

  i := i + 1;  
    
  end loop;
  
  if error_count > 1 then
  sys.dbms_system.ksdwrt (2, 'ORA-ERRORS BRINGING RBS ONLINE **************' );
  sys.dbms_system.ksdwrt (2, err_messages);
  sys.dbms_system.ksdwrt (2, 'This message written by sys.rollbacks_online');
  end if;
  
exception
	when others then
  	raise; --cause this to be rescheduled, if it's a job 
    
end rollbacks_online;

---

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...