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 <
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...