From : http://www.shutdownabort.com
This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.
- 1. Identify the rollback segments and tablespaces to be removed:
select segment_name , tablespace_name , status from dba_rollback_segs where tablespace_name != 'SYSTEM' /
- 2. Produce offline and drop statements for each rollback segment:
select 'alter rollback segment ' || segment_name || ' offline;' from dba_rollback_segs where tablespace_name = '&TS' and tablespace_name != 'SYSTEM' / select 'drop rollback segment ' || segment_name || ';' from dba_rollback_segs where tablespace_name = '&TS' and tablespace_name != 'SYSTEM' /
Review the output and, if you are happy with it, cut and paste it back into sqlplus.
- 3. Make a note of the old tablespace’s size and location, then drop it.
drop tablespace <tablespace_name> including contents and datafiles /
- 4. Create a new ‘undo’ tablespace in place of the old one.
create undo tablespace undo datafile '<path/file>' size <size> extent management local /
- 5. Update the initialisation parameters
If you are using a spfile (and you should be!) run the following commands:
alter system reset rollback_segments scope=spfile sid='*' / alter system set undo_management=auto scope=spfile / alter system set undo_tablespace=undo scope=spfile /
If you are still using a pfile (init.ora) then do the following:
Remove the following settings:
Add/alter these two lines:
- 6. Restart the instance
Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying ‘instance aborted’. The alert log will contain a slightly more detailed explanation.
Restart the instance:
shutdown immediate startup
Rerun the query from step 1. There will be a whole load of rollback segments with names like ‘_SYSSMU1$’. This is an indictaion that the instance is now using auto undo.
Filed under: Computer / IT