Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    39

    Unanswered: Tablespace Fragmentation problem

    All,

    I am working in Oracle 9i. Developers are facing some problem. Oracle is throwing ORA-1654 error. There is enough space in the tablespaces. To me it seems to be fragmentation problem. I found one query on some site and executed in my environment.

    SQL> select substr(ts.name, 1,10) TableSpace,to_char(f.file#,990) "file #",tf.blocks blocks,sum(f.length) free,to_char(count(*),9990) frags,max(f.length) bigst, to_char(min(f.length),999990) smllst,round(avg(f.length)) avg,to_char(sum(decode(sign(f.length-5), -1, f.length,0)),99990) dead from sys.fet$ f, sys.file$ tf, sys.ts$ ts where ts.ts# = f.ts# and ts.ts# = tf.ts# group by ts.name, f.file#, tf.blocks;

    TABLESPACE file BLOCKS FREE FRAGS BIGST SMLLST AVG DEAD
    ---------- ---- ---------- ---------- ----- ---------- ------- ---------- ------
    GAP_ARC 7 15360 8239 108 6099 20 76 0
    GAP_BILD 8 256000 223804 3655 77909 5 61 0
    GAP_DATA 9 230400 48267 211 3937 5 229 0
    GAP_GEN 10 192000 86156 902 52178 1 96 3
    GAP_IMP 11 38400 37669 14 37399 10 2691 0
    GAP_INDEX 12 230400 96408 3557 506 1 27 5335
    GAP_INDEX 12 409600 96408 3557 506 1 27 5335
    GAP_INDEX 13 230400 56120 1611 495 2 35 2627
    GAP_INDEX 13 409600 56120 1611 495 2 35 2627
    GAP_ZNZL 14 1920 1919 50 515 10 38 0
    RBS 3 51200 30479 871 35 29 35 0
    RBS_BIG 4 76800 63929 157 43649 130 407 0
    SYSTEM 1 25600 25952 2 12976 12976 12976 0
    SYSTEM 2 25600 22728 20 11321 2 1136 12
    TEMP 5 64000 58279 252 25649 130 231 0
    TOOLS 6 6400 6384 1 6384 6384 6384 0

    16 Zeilen ausgewählt.

    From the output it seems GAP_INDEX has some fragmentation problem. Can somebody suggest, what the output means particularly the "DEAD" one.

    And also, how to do the fragmentation ??

    Thanks for help.

    Regards,
    Rajeev

  2. #2
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Rajeev, doesn't matter if your tablespaces are too much fragmented... you cannot gain space enough to solve your problem, even if you do, the problem will show up later.

    You'll have to extent the datafile's size.

  3. #3
    Join Date
    Oct 2005
    Posts
    39
    Thanks for your reply.

    But what is the meaning of DEAD column in my post ??

    Please let me know.

    Regards,
    Rajeev Tomar

Posting Permissions

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