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