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

Rebuilding and modifying a database

I need to rebuild an Oracle 9.2.0.3 database on Windows 2003. During the rebuild I would like to move the primary keys out of the SYSTEM tablespace. I would also like to change from dictionary managed to locally managed tablespaces. What is the best way/steps to rebuild and modify this database?

I need to rebuild an Oracle 9.2.0.3 database on Windows 2003. I discovered user table primary keys located in the SYSTEM tablespace. Performance on the database has decreased drastically over the last few months. The SYSTEM tablespace has a fragmentation index of 21 out of 100 and the primary user tablespace has a fragmentation index of 7 out of 100. I believe the only way to correct the fragmentation problem with the SYSTEM tablespace is to rebuild the database. During the rebuild I would like to move the primary keys out of the SYSTEM tablespace. I would also like to change from dictionary managed to locally managed tablespaces. What is the best way/steps to rebuild and modify this database?
Start by moving the user primary keys out of the SYSTEM tablespace.

Next, convert the tablespaces by using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL. Leave the SYSTEM tablespace until last. Make sure that you take a full backup of your database before attempting this conversion!

You may find that there is no need to rebuild the database. What evidence do you have that poor performance is caused by fragmentation?

Dig Deeper on Oracle database performance problems and tuning

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