system tablespace fragmentation

[Tiffany Yu]

I am using Oracle 8.1.6. One of the database's system tablespace has fragmentation problem. Does anybody has this experience before and tell how to defragment system tablespace? Please provide detailed steps. Thanks.

[Joachim DIESTEL]

First you have to verify which objects are fragmented. If the fragmented objects are owned by yourself, no problem, recreate them in another tablespace otherwise you have lost because you have to recreate your database.

[Alexander M. Ermes]

Hi there.
If you want to get rid of the fragmentation, you have to export your data, recreate the database and import the the data.
If you want to resize the objects for users SYS and SYSTEM, look at the file
/home/dba/oracle/product/${ORACLE_SID}/rdbms/admin/sql.bsq
.
If you run Oracle Applications, you should also check the adprepdb.sql script.
RGds
Alexander M. Ermes

[Printaporn_1]

coalesce tablespace with
ALTER TABLESPACE tablespace COALESCE;

To display statistics about coalesceable extents for tablespaces, you can view the
DBA_FREE_SPACE_COALESCED view.

[Andreas D. Skjervold]

Hi

Defragmentation of tablespaces is done using export / import rutine (remember compress=n if you want to retain extend sizes)

Unfortunately this is not possible with the system tablespace, here you have to recreate to database to get a fresh start.

When working with fragmntation problems I would like to recomend Oracles SAFE Whitepaper
SAFE (Simple Algorithm for Fragmentation Elimination) is a seet of rules to apply during object creation (ie tablespace creation) that sets uniform extent size for your tablespaces, and yor defragmentation problems will be over.

Second; from 8i of you have the possibility to create localy managed tablespaces (where datadictionary information is retained in the tablespaces itself instead of in the system tablespace) this feature is inplace due to the transportable tablespace feature of Oracle, but also includes an option that quite interesting and that is uniform allocation!

Last I might mention that the Oracle Enterpise Manager also has a tool called Reorg Wizard. This does the reallocation of objects locally without export /import.
BUT this also does not support defragmentation of the system tablespace.

So the end of it all is that you'll have to export your database, recreate it and to a full import.
And if you want to live a quiet life afterward, start looking into SAFE or the 8i localy managed tablespaces with the uniform allocation policy (wich infact is SAFE)

Andreas

[Tiffany Yu]

Hi Andreas,
Could you tell me where I can find the Oracle "SAFE" whitepaper?
Thanks,
Tiffany

[Tiffany Yu]

Alexander and Andreas,

By recreating database, do you mean creating a brandnew empty database with a different instance name, then import the data to this new db?

Thanks,
Tiffany

[Roman_5]

If you are already getting oracle error pointing that it cannot extend one of oracle's system segments, I'd opt for allocating another datafile for system tablespace rather than perform export/import.

Run something like the following while connected as SYS or SYSTEM or any user with DBA privileges:
alter tablespace system add datafile '...' size 128M;

Export/Import can be time-consuming procedure for any but small-to-medium size databases. And normally size of system tablespace is a small percentage of a total database size.

I'd check though what had caused the fragmentation otherwise even if you recreate the database pretty soon you will be back to where you have started.
Normally system tablespace will not get fragmented unless some users are using it as their temporary tablespace or created some tables/indexes in it (and dropped them later).

I'd run the following:
1)
select owner, segment_name
from dba_segments
where tablespace_name = 'SYSTEM' and
owner not in ('SYS', 'SYSTEM',
'MDSYS', 'OUTLN', 'ORDSYS',
'AURORA$JIS$UTILITY', 'OSE$HTTP$ADMIN');
/

2)
select username, default_tablespace,
temporary_tablespace
from dba_users
where
(default_tablespace = 'SYSTEM' or
temporary_tablespace = 'SYSTEM')
/

I normally switch temporary tablespace for all users (including sys and system) for TEMPORARY TABLESPACE other than system tablespace.

Normally only accounts created by oracle install will have system tablespace as their default: SYS, SYSTEM, ORDSYS, MDSYS, AURORA$JIS$UTILITY, OSE$HTTP$ADMIN, DBSNMP etc.

You can separate users that created during install from others by running:
select username, trunc(created)
from dba_users
order by 2
/
The ones created at the tablespace creation day will be listed first.

You can change user's default or temporary tablespace by using:
alter user
default tablespace
temporary tablespace
/

[Andreas D. Skjervold · Accepted solution]

Hi
By export,recreating and importing I mean:
1. Do a full export (full=y compress=n)
2. Shutdown the database
3. Backup your databases datafiles (/u0?/oradata/ mountpoints)
4. Scrap your datafiles
5. Create a new database with the SAME instance name.
6. Perform a full import with the dump file from 1. (use parameter commit=y to avoid rollback segment filling up)

2. + 3. isnt nescessary but always a good precaution.

The SAFE withepaper is infact not easy to find , at the moment all I have is a printout. I will gety back to you on this...

[Andreas D. Skjervold]

SAFE whitepaper:
http://technet.oracle.com/deploy/availability/pdf/defrag.pdf

Oracle says:
---
This document is no longer available in the metalink database for customers.
Please refer to Oracle8i performance tuning manual for issues regarding defragmentation.
---
!!!

The new functionallity using UNIFORM allocation in tablespaces takes care of most issues regarding SAFE, the only thing you have to consider is the uniform extent size to use.

In the whitepaper the values;160K, 5120K, or 160M is used, but to make things confusing there are 2 versions of this Whitepaper out there, one stating 160K, 5120 ans 160M sizes and another stating 128K, 4M and 128M to use.

I use the latter, but the main point from a perfomance view to keep the number of extents under 1024 (this inspite the fact that there sin't any measurable degradation with several thousand extents!!)

Andreas

[Tiffany Yu]

Thank you all very much. This is really really helpful.

Andreas,
I found the SAFE white paper. FYI, it says in the article that the extent sizes 160k, 5120k, and 160m are recommended for version 7, and 128k, 4m, and 128k are recommended for verion 8. Thanks for your extra input.

Tiffany