Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Removing Fragmentation in SYSTEM Tablespace

SOLVED
Go to solution
Soumen Ghosh_1
Occasional Advisor

Removing Fragmentation in SYSTEM Tablespace

Hello,

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

Thanks in advance.

Soumen Ghosh
3 REPLIES
Andreas D. Skjervold
Honored Contributor
Solution

Re: Removing Fragmentation in SYSTEM Tablespace

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

Brian
Marcos_9
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;