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