Removing Fragmentation in SYSTEM Tablespace

[Soumen Ghosh_1]

Hello,

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

Thanks in advance.

Soumen Ghosh

[Andreas D. Skjervold · Accepted solution]

Hi

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....

Andreas

[Brian Crabtree]

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.

Brian

[Marcos_9]

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;