Q
Problem solve Get help with specific problems with your technologies, process and projects.

Moving from rollback segments to undo tablespace in Oracle 10g

We are using rollback segments in an Oracle 10g database. We would like to move from using rollback segments to the undo tablespace.

We are using rollback segments in an Oracle 10g database. For all good reasons, we would like to move from using rollback segments to the undo tablespace. Can you give some important steps involved in doing this move?
This type of move is pretty easy. Just follow these steps:
  1. Create an Undo tablespace:
    CREATE UNDO TABLESPACE undo_tbs 
    DATAFILE '/dir/undo_tbs01.dbf' SIZE xxM 
    EXTENT MANAGEMENT LOCAL;
    
  2. Modify your initialization parameters:
    ALTER SYSTEM SET undo_tablespace=UNDO_TBS SCOPE=spfile;
    ALTER SYSTEM SET undo_retention=xxxx SCOPE=spfile;
    ALTER SYSTEM SET undo_management=AUTO SCOPE=spfile;
    
  3. Bounce the instance.
  4. Remove the old rollback segments and their tablespace. Make sure you do not drop the SYSTEM rollback segment.

You will have to determine the optimal size for your Undo tablespace. Part of this is predicated on the undo_retention parameter, which you will also have to denote.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close