Fragmentation of Objects owned by system

[anuragmin]

Hi,

It seems that the system tables/indexes in my database have become fragmented. I know that the database needs to be recreated to fix this but whats steps do I need to take so that this does not happen again.

Below is the output of the query


select substr(segment_name,1,25)"SEGMENT NAME",extents,next_extent,bytes,
2 segment_type from sys.dba_segments where extents>4 and owner in
3 ('SYS','SYSTEM');

SEGMENT NAME EXTENTS NEXT_EXTENT BYTES SEGMENT_TYPE
------------------------- --------- ----------- --------- ------------
OBJ$ 5 106496 507904 TABLE
VIEW$ 6 106496 630784 TABLE
ARGUMENT$ 7 106496 753664 TABLE
SOURCE$ 86 204800 17424384 TABLE
IDL_UB1$ 99 106496 12058624 TABLE
IDL_CHAR$ 8 106496 876544 TABLE
IDL_UB2$ 65 106496 7880704 TABLE
IDL_SB4$ 19 106496 2228224 TABLE
DEPENDENCY$ 5 106496 507904 TABLE
HIST_HEAD$ 7 221184 466944 TABLE
C_OBJ# 8 204800 1597440 CLUSTER
C_FILE#_BLOCK# 7 221184 475136 CLUSTER
C_TS# 5 98304 180224 CLUSTER
C_COBJ# 5 98304 245760 CLUSTER
C_TOID_VERSION# 6 106496 630784 CLUSTER
I_COL1 9 106496 1015808 INDEX
I_COL2 7 106496 770048 INDEX
I_COL3 5 106496 524288 INDEX
I_ARGUMENT1 7 106496 753664 INDEX
I_SOURCE1 36 204800 7184384 INDEX
I_IDL_UB11 5 98304 180224 INDEX
I_IDL_UB21 5 98304 180224 INDEX
I_IDL_SB41 5 98304 180224 INDEX
I_OBJAUTH1 5 98304 180224 INDEX
I_OBJAUTH2 5 98304 180224 INDEX
I_DEPENDENCY1 5 106496 507904 INDEX
I_HH_OBJ#_COL# 6 147456 303104 INDEX
I_HH_OBJ#_INTCOL# 6 147456 303104 INDEX
SYSTEM 5 57344 409600 ROLLBACK
SYSROL 7 106496 860160 ROLLBACK
4.2 1111 40960 45506560 TEMPORARY

Thanks
Anurag

[jdorlon]

In short : Don't worry about it.

You can alter the next extent size of some of the objects (like SOURCE$ and its index) if you want, but 100 extents is nothing to worry about.

Tablespace fragmentation is not really a problem unless objects are being created and destroyed all the time, and this does not (well, SHOULD not) be happening in your system tablespace. Just make sure NO user has SYSTEM as a temporary tablespace and only SYS has it as a default tablespace.

If you really want to prevent this next time you create a database, you can adjust the initial and next extent sizes of these objects in the create scripts.

-John