I need to rebuild an Oracle 220.127.116.11 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?