Manual Rollback Segements Undo to Automatic Undo
Steps are as follows:
shutdown immediate;
startup;
select TABLESPACE_NAME, CONTENTS, STATUS from dba_tablespaces where contents=’UNDO’;
select ‘alter rollback segment ‘ || segment_name || ‘ offline;’ from dba_rollback_segs where segment_name like ‘%RBS%’;
Execute the result of the above sql query to take the rollback segments offline
drop tablespace rbs including contents and datafiles;
drop tablespace rbs_large including contents and datafiles;
CREATE UNDO TABLESPACE UNDOTBS DATAFILE ‘/u01/db_name/undotbs01.dbf ‘ SIZE 5G REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 8000M , ‘/u01/db_name/undotbs02.dbf ‘ SIZE 5G REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 8000M, ‘/u01/db_name/undotbs03.dbf ‘ SIZE 5G REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 8000M , ‘/u01/db_name/undotbs04.dbf ‘ SIZE 5G REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 8000M ;
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS scope=spfile;
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO scope=spfile;
shutdown immediate;
startup;
drop tablespace undotbs1 including contents and datafiles;
shutdown immediate;
startup;
