Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

Removing Fragmentation in SYSTEM Tablespace

Go to solution
Soumen Ghosh_1
Occasional Advisor

Removing Fragmentation in SYSTEM Tablespace


How to remove fragmentation in SYSTEM tablespace in Oracle 8i database?

Thanks in advance.

Soumen Ghosh
Andreas D. Skjervold
Honored Contributor

Re: Removing Fragmentation in SYSTEM Tablespace


Defragmentation of tablespaces is done by exporting user data, recreating the tablespaces and importing the data again.

Unfortunately this is not possible with the system tablespace, as this can't be removed and recreated.

If your system tablespace is so badly fragmented that you have to do something about it the only solution is the following:

-Do a full export of the database. (compress=n)*
-Remove the entire database
-Recreate the database from you original creation scripts (or by other means)
-Perform a full import.

(* setting compress=n prevents export of joinnig segment extents to one large extent)

As the import will recreate all user tablespaces, you infact just need to get inplace a basic database with system and rollback tablespaces. The rest will be recreated during import.

During import it might be wise to set parameter commit=Y to avoid rollback segments running full.

If you have Enterprise Manager,with the tuning pack, there is also a reorg wizard, that might do this by reallocation data in the database by createing temporary tables and moving data around.
But wizards are wizards....

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Brian Crabtree
Honored Contributor

Re: Removing Fragmentation in SYSTEM Tablespace

You shouldn't try to import/export the database to remove fragmentation of the SYSTEM tablespace. Overall, fragmentation of the SYSTEM tablespace shouldn't be worried about, as only SYSTEM and SYS owned objects should be in this tablespace.

If you are going to go with the previous response, you will want to recreate the database with a higher block size. The block size determines the size of the initial/next extent of the system objects. If the fragmentation problem on the database is caused by the number of objects, importing and exporting the database will not solve the problem, as the number of objects will not decrease.

Really though, my suggestion would be to not worry about the fragmenation, as it should not heavily affect performance.

Occasional Advisor

Re: Removing Fragmentation in SYSTEM Tablespace

Perhaps you should also check that no user has the system tablesapce as its default tablespace. You can do this with this sentence:

select username,default_tablespace from dba_users;