Fragmentation of Objects owned by system
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Fragmentation of Objects owned by system

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    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

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width